Explain this statement about filtering in SQL Server vs MySQL My company is in the midst of an IT overhaul and considering a shift away from MS SQL Server (2016 Standard) to a MySQL database (version 5.6.10). I have used MS SQL Server for years, so I am apprehensive to make this move. Basically - our IT team is under the impression that MySQL is free and comes with only a few syntax changes.

I have spent the day reading up on the differences and honestly finding that they seem to be very similar. If I am going to make a case for MS SQL Server - then now is the time to do it. One key detail - we are an analytical company and our SQL queries are often complex - so I need to make sure that our RDBMS of the future is up to par.

I have encountered the following statement multiple times (about filtering) and I need a translation. My interpretation of this is that it is not possible to use a single query to join tables from differing databases. However I have seen youtube videos where people do just that in MySQL. Please help me understand this difference and whether it is important or not.

  MySQL allow users to filter out tables, rows, and users in a number of ways. But it requires users to filter out the tables, rows, or users by individual databases. While filtering the data, the developers have to filter database tables individually by running multiple queries. On the other hand, SQL Server enables developers to take advantage of row-based filtering. The row-based filtering option filters data on a database by database way. Also, the filtered data is stored in a separate distribution database. Hence, it becomes easier for programmers to filter multiple rows without considering the number of databases.

  - A Comparison between MySQL vs. MS SQL Server

Greatly appreciate your help with deciphering the quoted statement as well as any Pro/Cons you see for this move. Thanks!

"Very few syntax changes" -- Excuse my while I laugh!  SQL Server has a lot of features that MySQL does not have.  Any use of such is not just a syntax change, but possibly a query rewrite.

TOP 10 --> LIMIT 10 -- but only approximately.

ROW_NUMBER --> does not exist

SEQUENCE --> AUTO_INCREMENT -- but only approximately

dbname.dbo.tablename --> dbname.tablename

Which version of MySQL?  MySQL 8.0 has a good chance of having some of the fancier features being used in SQL Server.  MariaDB 10.4 is likely to come even closer to SQL Server, in that it handles some of the incompatibilities that MySQL does not cover.

JOINing across databases on the same instance of MySQL has never been a problem:

SELECT a.this, b.that
    FROM db1.tblx AS a
    JOIN db2.tbly AS b
    WHERE a.foo = 1
      AND b.blat < 5;

JOINing across two separate instances (of MySQL or MariaDB) on the same server, or separate instances on separate servers is a problem.  See MariaDB's "Connect".  The quote you provided leaves the term "database" ambiguous.  (Or it was pulled out of context.)  I hope that I have made it clear with the addition of the term "instance".

As for the rest of that link.  It is aimed at CEOs, not us grunts who will have to make the transition.  Too much 'hand-waving' and fluff; no meat.

I have not figured out what it means by "row-based filtering".  Maybe it is a snazzy feature in SQL Server.  But maybe it is not being used by your code.

Start a new Question and post in it "how do I change this query to MySQL" together with a non-trivial, but typical query.  You may see some of the 4 incompatibilities I mentioned above, or there may be other things waiting to trip up the conversion.


Creating a database for a scenario involving students, teachers and courses I am undertaking a project that involves a student management system, but I am struggling to normalise/create relationships between certain tables. Here is my current setup:

Models

Users(id, first name, last name, gender, password, email)
Roles(id, name)
Course(id, name, level, day, start time, end time)

Attempted relationships (might be wrong)

User 1->has->N Roles (I think I need a user/roles bridging table)
Students(user role 6) N->has(studies)->M Courses
Teachers(user role 3-5) N->has(teaches)->M Courses

Example data

Table: Courses

id name    level    teacher   day start_time end_time students (0NF)
--+-------+--------+---------+---+----------+--------+--------------
1  English Beginner 1         Mo  12:00      13:00    101,102...
2  English Beginner 1         Tu  12:00      13:00    101,124...
3  English Beginner 1         Fr  13:00      14:00    101,105...
4  English Expert   1         We  14:00      15:00    145,155...
5  Maths   Single   2         Mo  12:00      13:00    135,163...
6  Maths   Single   3         Tu  13:00      14:00    192,123...
7  Maths   Full     3         Tu  15:00      16:00    134,101...

Example report

Student                       (Id = 101)
-----------------------------

Attends English for Beginners (Taught by Teacher 1)
-----------------------------
Mon - 12:00 to 13:00
Tue - 12:00 to 13:00
Fri - 13:00 to 14:00

Attends Maths (Full)          (Taught by Teacher 3)
-----------------------------
Tue - 15:00 to 16:00

Please forgive me for any inconsistencies in structure - I am a beginner in DB design and DBA StackExchange also. Could I have the steps explained to arrive at a solution?

"Roles" has a different meaning.
A bridging ('mapping') table is how specify a 'relationship' between two tables that is "many-to-many".  Example:  Students:Classes.
1-many works differently.  It needs a column in one table to link to the other.  Example:  Worker:Manager

In designing an "Entity-Relationship" ("ER") schema do something like this:

One table per "entity":  Student, Teacher, Course, Class.  (This assumes there are no 'student teachers'.)  Each entity table would have numerous columns for attributes such as name (most tables), date (for class),
Establish the relations between them.  Many:many:  Student:Class, Teacher:Class; 1:many: Class:Course

Building a list of students as 101,103,... is best done via a JOIN and GROUP BY and GROUP_CONCAT(), not by having a column with such a list.

Since a "class" might multiple times a week need to be another table session, and 1:many for class:session.


how to use database mail from SSIS I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks

The SSIS Send mail task connects directly to a SMTP server - it doesn't use database mail.  If you want to use database mail, you would invoke it through an 'Execute T-SQL Statement' task that calls a stored procedure that wraps the database mail facility, or something similar.


How to start mysqld with read-only data directory? I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag to my.conf, I get the following errors:

root@2380610236d1:/# mysqld --innodb-read-only
140807 19:19:38 [Warning] Can't create test file /var/lib/mysql/2380610236d1.lower-test
140807 19:19:38 [ERROR] mysqld: Can't create/write to file '/var/lib/mysql/aria_log_control' (Errcode: 13 "Permission denied")
140807 19:19:38 [ERROR] mysqld: Got error 'Can't create file' when trying to use aria control file '/var/lib/mysql/aria_log_control'
140807 19:19:38 [ERROR] Plugin 'Aria' init function returned error.
140807 19:19:38 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
140807 19:19:38 [Note] InnoDB: Started in read only mode
140807 19:19:38 [Note] InnoDB: Using mutexes to ref count buffer pool pages
140807 19:19:38 [Note] InnoDB: The InnoDB memory heap is disabled
140807 19:19:38 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
140807 19:19:38 [Note] InnoDB: Compressed tables use zlib 1.2.8
140807 19:19:38 [Note] InnoDB: Using Linux native AIO
140807 19:19:38 [Note] InnoDB: Using CPU crc32 instructions
140807 19:19:38 [Note] InnoDB: Disabling background IO write threads.
140807 19:19:38 [Warning] InnoDB: Unable to open "./ib_logfile0" to check native AIO read support.
140807 19:19:38 [Warning] InnoDB: Linux Native AIO disabled.
140807 19:19:38 [Note] InnoDB: Initializing buffer pool, size = 256.0M
140807 19:19:38 [Note] InnoDB: Completed initialization of buffer pool
2014-08-07 19:19:38 7f2bc5a247c0  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
140807 19:19:38 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
140807 19:19:38 [ERROR] InnoDB: The system tablespace must be writable!
140807 19:19:38 [ERROR] Plugin 'InnoDB' init function returned error.
140807 19:19:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140807 19:19:38 [Note] Plugin 'FEEDBACK' is disabled.
140807 19:19:38 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Couldn't start tokudb because some other tokudb process is using the same directory [/var/lib/mysql/] for [environment]
140807 19:19:38 [ERROR] Plugin 'TokuDB' init function returned error.
140807 19:19:38 [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.
140807 19:19:38 [ERROR] Unknown/unsupported storage engine: InnoDB
140807 19:19:38 [ERROR] Aborting

140807 19:19:38 [Note] mysqld: Shutdown complete

How can I start the database server with a read-only data directory?

Please note the InnoDB Architecture (Pictorial Representation by Percona CTO Vadim Tkachenko)

The system tablespace file ibdata1 has moving parts requiring writes

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs

It's good you have --innodb-read-only set to InnoDB handle read-only situations.

You also need to make sure innodb_change_buffering is 0.

You also needed to make sure that crash recovery never happens. Here is how to set  it up.

First, with the database on a writeable media, login to MySQL and run this:

mysql> set global innodb_fast_shutdown = 0;

Then, go to the OS and run

service mysql stop

With innodb_fast_shutdown enabled, this cause mysqld to purge everything and its grandmother out of the InnoDB plumbing upon shutdown.

Next, move the datadir to the readonly media.

Then, add this to my.conf

[mysqld]
innodb_change_buffering = 0
innodb_read_only = 1

Finally, go start mysql with service mysql start and InnoDB should start very quickly because there will be no need to perform crash recovery since everything was purged out of the InnoDB plumbing beforehand.


MySQL 5.6 Datetime Range According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', I received no error and the values were successfully stored in the table.

CREATE TABLE `test` (
  `a` datetime(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

mysql> insert into test values ('999-01-01 01:01:01');
Query OK, 1 row affected (1.18 sec)

mysql> select * from test;
+-------------------------+
| a                       |
+-------------------------+
| 0999-01-01 01:01:01.000 |
+-------------------------+

mysql> insert into test values ('1-01-01 01:01:01');
Query OK, 1 row affected (0.08 sec)

mysql> select * from test;
+-------------------------+
| a                       |
+-------------------------+
| 0999-01-01 01:01:01.000 |
| 0001-01-01 01:01:01.000 |
+-------------------------+

Does this mean that the documentation is wrong? I am using mysql version 5.6.21.

OBSERVATIONS

According to the MySQL 5.0 Documentation

  As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 5.1.7, “Server SQL Modes”, for more information.

  Before MySQL 5.0.2, the MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to '0000-00-00'. Please note that this still permits you to store invalid dates such as '2002-04-31'. To ensure that a date is valid, you should perform a check in your application.

The second paragraph is not in the 5.1, 5.5, or 5.6 Documentation. This may have been left out of the Documentation when MySQL 5.1 went GA. If this is still happening, this is a big oversight.

Let try this out on MySQL 5.5.37

mysql> create database bluecollarcoder;
Query OK, 1 row affected (0.00 sec)

mysql> use bluecollarcoder;
Database changed
mysql> CREATE TABLE `test` (
    ->   `a` datetime NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('999-01-01 01:01:01');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values ('1-01-01 01:01:01');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+---------------------+
| a                   |
+---------------------+
| 0999-01-01 01:01:01 |
| 0001-01-01 01:01:01 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.37-log |
+------------+
1 row in set (0.00 sec)

mysql>

I just did this on my laptop running MySQL 5.5.37 for Windows. Same Problem.

You will have to check for invalid dates in your code.

SUGGESTION

If you want to fix all dates so that invalid datetimes are defaulted to a valid datetime, you can run this:

update test set a = from_unixtime(unix_timestamp(a));

When I ran it, it set to FROM_UNIXTIME(0) in my timezone

mysql> update test set a = from_unixtime(unix_timestamp(a));
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select a,unix_timestamp(a) from test;
+---------------------+-------------------+
| a                   | unix_timestamp(a) |
+---------------------+-------------------+
| 1969-12-31 19:00:00 |                 0 |
| 1969-12-31 19:00:00 |                 0 |
+---------------------+-------------------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


SQL Server Row by Row Access I have a table structured as so (Simplified)

Name, EMail, LastLoggedInAt

I have a user in SQL Server (RemoteUser) that should only be able to see data (Via a select query) where the LastLoggdInAt field is not null.

It looks like I can do this? Is it possible?

The SQL Server security model allows you to grant access to a view without granting access to the underlying tables.
Since example code is a great way to show a concept, consider the following, with a LoginDetails table, and corresponding view:
CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE VIEW dbo.LoginDetailsView
AS
SELECT ld.Username
    , ld.EmailAddress
    , ld.LastLoggedInAt
FROM dbo.LoginDetails ld
WHERE ld.LastLoggedInAt IS NOT NULL;
GO

We'll create a login, and a user, then assign that user the rights to select rows from the view, without having any rights to view the table itself.
CREATE LOGIN RemoteUser
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetailsView TO RemoteUser;

Now, we'll insert two test rows:
INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', 'x@y.com', NULL)
    , ('user y', 'y@y.com', GETDATE());

This tests the security model.  The first SELECT statement succeeds, since it is selecting from the view, whereas the second SELECT statement fails because the user does not have direct access to the table.
EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetailsView;

╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user y   ║ y@y.com      ║ 2018-02-15 07:36:54.490 ║
╚══════════╩══════════════╩═════════════════════════╝

SELECT *
FROM dbo.LoginDetails;

REVERT

Note the results from the view exclude the row where the LastLoggedInAt value is NULL, as required in your question.
The second SELECT statement against the underlying table returns an error:

Msg 229, Level 14, State 5, Line 28
The SELECT permission was denied on the object 'LoginDetails', database 'tempdb', schema 'dbo'.

Cleanup:
DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP VIEW dbo.LoginDetailsView;
DROP TABLE dbo.LoginDetails;

Alternately, if you have SQL Server 2016 or newer, you could use a row-level-security-predicate to prevent certain users seeing rows with a NULL LastLoggedInAt value.  The Microsoft Docs for Row Level Security is here.
First, we create the table, a login, a user for that login, and we grant access to the table:
CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE LOGIN RemoteUser
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetails TO RemoteUser;

Next, we insert a couple of sample rows.  One row with a null LastLoggedInAt, and one with a non-null value for that column.
INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', 'x@y.com', NULL)
    , ('user y', 'y@y.com', GETDATE());

Here, we're creating a schema-bound table-valued-function that returns a row with either 0 or 1 depending on the value of the @LastLoggedInAt and @username variables that are passed into the function.  This function will be used by a filter-predicate to eliminate the rows we want to hide from certain users.
CREATE FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate
(
    @LastLoggedInAt datetime
    , @username sysname
)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE (@username = N'RemoteUser' AND @LastLoggedInAt IS NOT NULL)
        OR @username <> N'RemoteUser';
GO

This is the security filter that eliminates rows from SELECT statements ran against the dbo.LoginDetails table:
CREATE SECURITY POLICY LoginDetailsRemoteUserPolicy
ADD FILTER PREDICATE dbo.fn_LoginDetailsRemoteUserPredicate(LastLoggedInAt, USER_NAME())
ON dbo.LoginDetails
WITH (STATE=ON);

The filter above uses the dbo.fn_LoginDetailsRemoteUserPredicate function by passing in the name of the current user, along with the values from each row for the LastLoggedInAt column from the dbo.LoginDetails table.
If we query the table as a normal user:
SELECT *
FROM dbo.LoginDetails

we see all rows:
╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user x   ║ x@y.com      ║ NULL                    ║
║ user y   ║ y@y.com      ║ 2018-02-15 13:53:42.577 ║
╚══════════╩══════════════╩═════════════════════════╝
However, if we test as the RemoteUser:
EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetails

REVERT

we only see "valid" rows:
╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user y   ║ y@y.com      ║ 2018-02-15 13:42:02.023 ║
╚══════════╩══════════════╩═════════════════════════╝
And, we clean up:
DROP SECURITY POLICY LoginDetailsRemoteUserPolicy;
DROP FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate;
DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP TABLE dbo.LoginDetails;

Be aware that schema-binding a function to the table in this way does make it impossible to modify the definition of the table without first dropping the filter predicate, and the dbo.fn_LoginDetailsRemoteUserPredicate function.


The message "Running the Merge Agent process would exceed the maximum number of processes allowed" appears, but it doesn't seem likely The full text of the message is "Running the Merge Agent process at this time would exceed the maximum number of processes allowed by this publication. The process will run when resources become available." It couldn't be put on the title.

We have a merge publication on a database. While a subscriber is synchronising, the above message often appears and synchronisation stalls for several minutes and might even time out.

The publication's "maximum concurrent processes" setting is 20, and using SSMS's activity monitor I see only 4 processes that seem to be related to this publication. In any case, there is only one subscriber for the publication and so it seems unlikely it could be using 20 processes.

Am I correct in my assumption above (that one subscriber shouldn't use 20 processes) and, if I am, what could be causing this problem?

Searching Google for the message's text brings only two results.

This is a merge publication where both publisher and subscriber use SQL Server 2008 R2.

For merge replication issues, first stop is to read Chris Skorlinski's blog post at http://blogs.msdn.com/b/repltalk/archive/2010/06/28/merge-replication-capacity-planning.aspx

He links you to several resources at the top of his blog post.  The post is from 2010, but merge replication has not had any significant update as far as I know.

Chris's bottom line is that there are too many variables: complexity of queries, power of the server, concurrent processes, et cetera, to be able to provide a recommended configuration. For example: "I (have) seen simple designs with little or no filtering supporting 1000 of users to very complex filtering/join design barely able to hand 10 subscribers."

One link that he particularly recommends is: http://robtiffany.com/mobile-merge-replication-performance-and-scalability-cheat-sheet/

Rob's focus is on merging replication to a SQL Server for later shipping to SQL Compact.  But focus on the issues of merge replication and he has several suggestions, such as:

Ensure your schema is sufficiently de-normalized so that you never have to perform more than a 4-way JOIN across tables.
In order to prevent SQL Server from performing Metadata Cleanup every time a Subscriber synchronizes, set the –MetadataRetentionCleanup parameter to 0.
As SQL Server has to scale up ... locking contention will increase due to more (concurrent) Merge Agents.  Adjust the parameters of the Default Profile so that both  –SrcThreads and –DestThreads are equal to 1.

Note, I do not use merge replication.  But by the nature of the issues with merge, there are more resources needed than in standard replication.


Using SQL Profiler on a database that's in production As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems.

But I've always used it on my development environment, and in a very controlled way.

Start my application, and get it into a specific state
Start a trace on the profiler
Perform a specific sequence of actions on my application
Stop the trace and examine the results.

Can the SQL Profiler be practically used in an in-production environment?

My first concern is that it would degrade the performance.

My second concern is that, because it's in production, you aren't triggering the interesting actions itself. You would have to leave the profiler running for a long period then analyse the results. Would the result set become too unwieldy? (Taking up too much disk space and being too hard to query).

Does anyone use the SQL Profiler in production?

I use SQL Profiler against production all the time.  When done correctly (filtering so that you get back a very small amount of data) against a server the risk is minimal.  Tracing everything down would be useless.


Replication : Which subscription changed a row Is it possible to query the replication metadata to see which subscription caused a change to a specific row in a table?

If the change hasn't yet been replicated you can look in the distribution database to see which publication will change the value.

If the subscriber is in full or bulk_logged recovery you can look in the transaction log to see what user made the change (it might have the application name in there, if it does that'll tell you what SQL Agent job made the change and therefor which publication and subscription made the change).  If you are in simple recovery mode you are hosed unless you are logging this data yourself somewhere within the subscriber.


SQL Server Maintenance Plan multiple database backups in a single folder I am using SQL Server 2008 R2. I am trying to create a maintenance plan which backs up multiple databases into one single folder for a day.

For ex. I need to backup db1, db2, db3 and dump all these 3 .bak files into a single folder with a date.

Is this possible?

Any help would be really help. Thanks in advance.

Yes it is possible
Use the Maintenance plan wizard its pretty easy to use.
Set your schedule type to recurring and frequency to daily on the "new job schedule form"
when you get to the "Define Back Up Database Task" form select the databases you wanna backup and select "create a backup file for every database " then set path of the folder you want to save to.

hope that helps


Select XML values when attribute involved I have a table that stores XML values in field: OtherData.

An XML value from one row:

< file path="C:\WINDOWS\system32\svchost.exe" />

I would like to select all rows that have an attribute path like '%svchost%'

What is the right SQL query to do this?

Use the XQuery CONTAINS method, like this:

SELECT CASE @x.exist
(
N'/bikes/Product[1]/Specifications[contains(., "Novice")]'
)
WHEN 1 THEN N'The bike is good for novices'
WHEN 0 THEN N'The bike is for more advanced riders'
END;

Good luck.


Why should a CTE start with a semi-colon? I was just looking at a post on StackOverflow where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand.  My question is, why does the first line of the CTE begin with a semi-colon?

;WITH n AS (SELECT TOP (10000) n FROM
  (SELECT n = ROW_NUMBER() OVER
    (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
  ) AS x ORDER BY n
)
SELECT n FROM n ORDER BY n; -- look ma, no gaps!

Is this to ensure the WITH statement does not get parsed into a previous SELECT or something?  I see nothing in SQL Server 2005 BOL about using a semi-colon prior to the WITH.

I always do it when posting here or on StackOverflow because for WITH - since the keyword is overloaded - the previous command requires a terminating semi-colon. If I paste a code sample that uses a CTE, inevitably some user will paste it into their existing code, and the previous statement won't have the semi-colon. So the code breaks, and I get complaints like:

  Your code broke! I got this error message: Incorrect syntax near 'WITH'...

While I'd like to believe that folks are becoming better about always terminating their statements with a semi-colon, I'd rather pre-empt the noise and just always include it. Some people don't like it, but <shrug />. You can include as many semi-colons before or after a valid statement as you want. This is valid:

;;;;SELECT 1;;;;;;;;;;;;SELECT 2;;;;;;;;SELECT 3;;;;;

So there is no harm in there being an extra semi-colon preceding a statement that by definition requires it. It is safer to do so even if it ain't so pretty.

It has to be worded weirdly to get the point across, but "not ending a valid statement with a semi-colon" is actually deprecated since SQL Server 2008. So as I describe in the blog post I link to above, even in cases where it's not required to bypass an error, it should be used wherever valid. You can see this here (and this entry has been there for multiple versions):

Deprecated Database Engine Features in SQL Server 2016(search this page for semicolon)

Of course it wouldn't be SQL Server if there weren't exceptions. Try this:

BEGIN TRY;
  SELECT 1/1;
END TRY;
BEGIN CATCH;
  SELECT 1/1;
END CATCH;

It's not the only exception to the rule but it's the one I find most unintuitive.


How to query all rows of a table into single json Postgresql? I am trying to generate a single json by querying from a table with id as key and email as value like this

{1: "abc@example.com", 2: "abcd@example.com"}

My attempt to create is:

select json_agg(json_build_object(id, email))
from auth_user
where id in (4733, 3625, 4961, 4237);

which results me

[{4733: "abc@example.com"}, {3625: "abcd@example.com"}]

Migrating a comment to an answer.

  I think you are looking for json_object_agg not json_agg – a_horse_with_no_name 2 hours ago

Links

JSON Functions and Operators


PostgreSQL 8.3: Slow GROUP BY on large table I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow...

select run_id, count(*) from result group by run_id;

I have an index defined on the run_id column.

How can I speed this up?

So why does Postgres 9.2 still show a sequential scan? I quote the Postgres Wiki:

  Is "count(*)" much faster now?

  A traditional complaint made of PostgreSQL, generally when comparing
  it unfavourably with MySQL (at least when using the MyIsam storage
  engine, which doesn't use MVCC) has been "count(*) is slow".
  Index-only scans can be used to satisfy these queries without there
  being any predicate to limit the number of rows returned, and without
  forcing an index to be used by specifying that the tuples should be
  ordered by an indexed column. However, in practice that isn't
  particularly likely.

  It is important to realise that the planner is concerned with
  minimising the total cost of the query. With databases, the cost of
  I/O typically dominates. For that reason, "count(*) without any
  predicate" queries will only use an index-only scan if the index is
  significantly smaller than its table. This typically only happens when
  the table's row width is much wider than some indexes'.

Emphasis mine.
There is hardly anything to gain from an index scan here, as long as your talbe isn't bloated with more (big) columns.


Amazon RDS low in memory no mysql connections I'm running the free instance of amazon RDS connection to a t2.medium ec2 instace. We are running a wordpress website with 20 active plugins and about 800 pages. I can see that I am low in memory (freeable memory) down to 52MB. There is only 1 database connection active and the google analytics real time report show only 1 visitor which is me.

mysql show process list returns

+-----+-------+-------------+-------+---------+------+-------+------------------+
| Id  | User  | Host        | db    | Command | Time | State | Info             |
+-----+-------+-------------+-------+---------+------+-------+------------------+
| ****| ***** | l****:46109 | mysql | Sleep   | 4    |       | NULL             |
| ****| ****  | ****:33918  | NULL  | Query   | 0    |  init | show processlist |
+-----+-------+-------------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

what is consuming my memory?

The memory will be consumed by a number of things, including the MySQL innodb buffer pool, table and definition caches, etc.

RDS will default the innodb_buffer_pool_size to {DBInstanceClassMemory*3/4}, which is a good starting point, but does only leave a 1/4 of the RAM available for other parts of the system.

One thing that really hits the small RDS instances is the performance_schema - this can consume 500Mb RAM as soon as the server starts up, and never releases it.

If you are not using the performance_schema regularly, if at all, turn it off by setting the appropriate parameter within your RDS parameter group.

You will need to reboot the RDS instance if this is the case.

At the end of the day, a t2.medium instance only has 4Gb of RAM available to you (see http://www.ec2instances.info/rds/), and 52Mb is probably more than enough free for your system to continue to work without issue. Most of your data content will be in the buffer pool, so MySQL will respond directly from memory for most of your queries, and feel pretty responsive, even on such a small system.


How do you filter SQL error logs to display specific error messages? I find it hard to believe that there aren't better native tools to filter SQL Server Error Logs.  Even Windows Logs have better filtering capabilities.  I'd like the ability to remove certain events from the results or a list of events from the results or to be able to search for a very specific error/event.

Here is one I wrote myself years ago.  This is a section of a larger stored procedure, but it works fine on its own.  You can modify the filters to remove unwanted entires as well as highlight others.

print ' '
print ' '
print '==================='
print '== SQL Errorlogs =='
print '==================='
print ' '

declare
  @FirstLog      smallint,
  @LastLog       smallint,
  @BufferRecords smallint,
  @SQL           varchar(2000),
  @Output        varchar(2000)

select
  @FirstLog      = 0, --> Defaults to current logfile
  @LastLog       = 1, --> Defaults to logfile immediately preceding current
  @BufferRecords = 4  --> Used to give a frame of reference to the error message

---------------------------------------------------------------------------------------------------
-- Date Created: July 11, 2005
-- Author:       Bill McEvoy
-- Description:  This procedure combines and parses the SQL Error Logs.  Entries of special
--               interest are marked which allows the operator to quickly scan the output for
--               errors.  Entries of interest are removed from the output.  Entries before and
--               after the errors are included to give a frame of reference.
--
---------------------------------------------------------------------------------------------------
-- Date Revised: December 7, 2006
-- Author:       Bill McEvoy
-- Reason:       I converted this procedure to support SQL Server 2005
---------------------------------------------------------------------------------------------------
set nocount on
declare @count  smallint,
        @alert  char(5)

select  @count  = 0,
        @SQL    = '',
        @output = '',
        @alert  = '---->'

---------------------------------------------------------------------
-- Validate input parameters                                       --
---------------------------------------------------------------------

IF (@FirstLog > @LastLog)
BEGIN
  select @lastLog = @FirstLog + 1
END

---------------------------------------------------------------------
-- M A I N   P R O C E S S I N G                                   --
---------------------------------------------------------------------
--                                                                 --
-- Create work tables                                              --
--                                                                 --
-- Import SQL Error Logs                                           --
--                                                                 --
-- Remove unwanted entries                                         --
--                                                                 --
-- Mark items of interest                                          --
--                                                                 --
-- Generate report                                                 --
--                                                                 --
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Create work tables                                              --
---------------------------------------------------------------------

IF (object_id('tempdb..#ERRORLOG') IS NOT NULL)
  drop table #ERRORLOG

CREATE TABLE #ERRORLOG
(
  LogID       int identity primary key clustered,
  LogDate     datetime NULL,
  ProcessInfo varchar(12) NULL,
  Alert       char(5) default ' ',
  LogEntry    varchar(900) NULL,
  Row         smallint NULL

)

-- Add indexes
create index [IX_ERRORLOG_Alert]    on dbo.[#ERRORLOG](Alert)    with fillfactor = 98 on [PRIMARY]
create index [IX_ERRORLOG_LogEntry] on dbo.[#ERRORLOG](LogEntry) with fillfactor = 98 on [PRIMARY]

---------------------------------------------------------------------
-- Import SQL Error Logs                                           --
---------------------------------------------------------------------

select @count = @FirstLog

  WHILE (@Count <= @LastLog)
  BEGIN
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')
    select @output = '-- Processing: ERRORLOG' + case(@count) when 0 then '    ' else '.' + convert(char(3), @count) end + ' --'
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',@output)
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
    select @SQL = 'exec master..sp_readerrorlog ' + convert(varchar(3), @count)
    insert into #errorlog (LogDate, ProcessInfo, LogEntry)
    execute (@SQL)
    select @count = @count + 1
  END

---------------------------------------------------------------------
-- Remove unwanted entries                                         --
---------------------------------------------------------------------

delete
  from #ERRORLOG
 where LogEntry like '%Bypassing recovery for database%'
    or LogEntry like '%Setting database option ANSI_WARNINGS%'
    or LogEntry like '%Log backed up with following information:%'
    or LogEntry like '%Login succeeded for user%'
    or LogEntry like '%found 0 errors and repaired 0 errors%'
    or LogEntry like '%Database differential changes%'
    or LogEntry like '%The certificate was successfully loaded%'
    or LogEntry like '%this is an informational message only%'
    or LogEntry like '%logging sql server messages in file%'
    or LogEntry like '%Recovery complete.%'
    or LogEntry like '%Starting up database ''ERROR_LOGGING''%'
    or LogEntry like '%Database backed up: Database: %'
    or LogEntry like '%Log backed up: Database: %'
    or LogEntry like '%allocate%'

---------------------------------------------------------------------
-- Mark items of interest                                          --
---------------------------------------------------------------------

update #ERRORLOG
   set Alert = @alert
 where (LogEntry like '%err%'
    or  LogEntry like '%exception%'
    or  LogEntry like '%violation%'
    or  LogEntry like '%warn%'
    or  LogEntry like '%kill%'
    or  LogEntry like '%dead%'
    or  LogEntry like '%encounter%'
    or  LogEntry like '%cannot%'
    or  LogEntry like '%could%'
    or  LogEntry like '%fail%'
    or  LogEntry like '%full%'
    or  LogEntry like '%not%'
    or  LogEntry like '%terminate%'
    or  LogEntry like '%bypass%'
    or  LogEntry like '%recover%'
    or  LogEntry like '%roll%'
    or  LogEntry like '%upgrade%'
    or  LogEntry like '%victim%'
    or  LogEntry like '%stop%'
    or  LogEntry like '%shut%'
    or  LogEntry like '%timed out%'
    or  LogEntry like '%truncate%'
    or  LogEntry like '%terminat%')
   and  (ProcessInfo <> ' ' or  ProcessInfo IS NULL)

---------------------------------------------------------------------
-- Generate report                                                 --
---------------------------------------------------------------------

BEGIN

  -- Show entries of interest + buffer records
  select A.LogID,
         'Date'       = isnull(convert(varchar(19), A.LogDate, 120), ''),
         A.ProcessInfo,
         A.Alert,
         'Descripton' = left(A.logEntry, 250)
    from #ERRORLOG A
   join (select LogID from #ERRORLOG where Alert = @alert) B  on (A.LogID >= (B.LogID - @BufferRecords))
                                                           and (A.LogID <= (B.LogID + @BufferRecords))

  -- Show informational records
  union
  select A.LogID,
         'Date'       = isnull(convert(varchar(19), A.LogDate, 120), ''),
         A.ProcessInfo,
         A.Alert,
         'Descripton' = left(A.logEntry, 250)
    from #ERRORLOG A
   where ProcessInfo = ''
   order by A.LogID
END


Prevent overwriting when using COPY As the title said. I found it inconvenient when accidentally rerun a copy (time-consuming query) to '\tmp\foo.csv'. I tried setting set -o noclobber in .bashrc but it did not help.

How can I prevent copy from overwriting existing file?

I am using Postgres 10. The doc seems not giving any info.

There is no such feature built into PostgreSQL.

You could use the PROGRAM option to do something like this:

copy pgbench_branches to program 'test -f /tmp/foo && exit 1|| cat > /tmp/foo';

But that is pretty undesirable as you have to enter the filename twice so it could get out of sync. And if you can't remember that you already exported the data, you probably can't remember to code it this way in the first place, unless it is encapsulated into a script.

If you compile your own server,  it would be pretty easy to hack the server to have this feature (example for linux):

diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 4f04d12..552fb81 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1915,7 +1915,7 @@ BeginCopyTo(ParseState *pstate,
            oumask = umask(S_IWGRP | S_IWOTH);
            PG_TRY();
            {
-               cstate->copy_file = AllocateFile(cstate->filename, PG_BINARY_W);
+               cstate->copy_file = AllocateFile(cstate->filename, "wx");
            }
            PG_CATCH();
            {

But running a customized server comes with its own cost (you have to reapply it to ever minor version update, you become reliant on features that don't exist outside your own set up, new people have to be trained on your peculiarities, etc.).


When do I have to use archive_command and when not I have some doubts about PostgreSQL replication command. I am doing some research and I found severals configurations.

first one is:

archive_mode = on
archive_command = 'cd .'

second one is :

cp -i %p /var/lib/postgresql/9.1/archive/%f'

Then my questions are:

What is the different?
The first one option ( command ) is really strange to me. What do it
do?

The first one (cd .) does nothing. It's a placeholder. It would be better to write true in my view, so it's more obviously intended as a no-op.

Turning archive_mode on but setting a no-op archive_command is a hacky workaround for the fact that changing archive_command only requires a server reload, but changing archive_mode requires a full server restart that can be disruptive in production.

The purpose of the archive_command, and all the rest, is covered in the documentation so I won't repeat all that material there.


Selecting rows from one table not in another table I have two tables

table_a_mod (2 million rows)
table_a (2 million rows + new live data)

table_a_mod is identical to table table_a except for two extra columns

table_a is a "live" table with data being written to it semi-regularly.

To keep table_a_mod up to date I would like to copy data from table_a to table_a_mod

By using a regular join, I am able to select the data:

SELECT *
FROM table_a t1
LEFT JOIN table_a_mod t2
USING(id)
WHERE t2.id IS NULL

However, this query takes about 1 minute to run.

Is there a more efficient query along the lines of:

SELECT  *
FROM table_a t1
LEFT JOIN
(
  SELECT id
  FROM table_a_mod
  ORDER BY id DESC
  LIMIT 1
) t2
USING (id)
WHERE t1.id > t2.id

ie. Get the largest id from table t2. Select all rows from table t1 with id greater than that number.

I've tried different permutations of the above query but I'm falling short.

Edit 1

In response to @danblack

EXPLAIN {slow_query}

"1" "SIMPLE"    "table_a"   "ALL"   \N  \N  \N  \N  "2353123"   ""
"1" "SIMPLE"    "table_a_mod"   "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "db.table_a.id" "1" "Using where; Not exists"

  I'm assuming id is a primary key in both

Yes. id is a primary key in both.

  Are the tables innodb and is there sufficient innodb_buffer_pool_size to hold both tables?

Both tables are innodb. And innodb_buffer_pool_size is 8388608

  Is "new live data" always of an id above the previous table?

Yes, because it's auto incrementing

  Why are you keeping two nearly identical tables

It is a temporary situation. We will drop the older one and use the newer one.

How much RAM do you have?  If you have more than 4GB, set innodb_buffer_pool_size to 70% of available RAM.  This is the most important thing to tune.  With it set to only 8M, you are definitely I/O-bound.  Increasing the size will probably shrink the time to under 10 seconds.

Your second idea should be good (assuming the only new rows have higher ids), but let's get rid of the LEFT and use MAX:

SELECT  *
    FROM  table_a t1
    JOIN
        ( SELECT  MAX(id) AS max_id
            FROM  table_a_mod ) t2
    WHERE  t1.id > t2.max_id


RMAN DUPLICATE from backup without Target or Catalog: How is the aux instance created? I am trying to duplicate an Oracle database based on backups to test if they can be used in case of complete system failure. Hence, I want to do this without a connection to the source database. I'm not using a Recovery Catalog.

I've read through all of the manual, but am stuck at step 7 of "Preparing to duplicate a database" on the fourth point:

  Start SQL*Plus and connect to the auxiliary instance as a user with SYSDBA or SYSBACKUP privileges.

The auxiliary server I am duplicating to is a clean empty installation with no database set up yet, as the manual mentions at Step 3:

  Install an Oracle Database that is the same release as the source database. Do not create a database; install the software only.

Now, when running

sqlplus "/ as sysdba"

I get

ORA-12560: TNS:protocol adapter error

Not entirely unexpected, as at no point have I set up a listener or Oracle service. So... what did I mis? Did I misunderstand the part about not creating a database? Is "a database" not the same as "an instance"? Did I overlook a part of the manual? And, what should I still install/create in order to have an "aux instance" i can start in nomount?

EDIT: This is in a Windows-server environment

That is the typical error message you get on Windows without properly setting up the environment.

While the steps in the manual may work flawlessly on Linux/UNIX, on Windows you need extra steps. On Windows, you need to create a service with oradim, start it (if it was not started), and after that you can use sqlplus / as sysdba.

The bare minimum is:

set ORACLE_HOME=...
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=test

oradim -new -sid test

This should automatically start the service, but if it did not:

sc start Oracleservicetest

And after the above, you can login to the idle instance with:

sqlplus / as sysdba


During installation can't change directory I try to install mysql db server on windows 10.

Version mysql-installer-community-5.7.16.0.msi

During the installation Install MSI does not offer to change the installation directory.

Do I correctly understand that I can't change the installation directory during the installation process?

There are two methods available, either use ZIP archive to install or you may have to change the datadir variable in my.ini after installation. You can use following steps

Complete the installation
Identify the config file and then look for related datadir being used, you may used mysql>show variables like ‘datadir’ within mysql to identify datadir or the service details for concerned my.ini
Stop the service if it is already running
Change the value of datadir in my.ini, it is usually placed under C:\ProgramData\MySQL
Copy/move the folder contents to newer location
Make sure permissions are provided to user who owns mysql service
Start service and verify using mysql>show variables like ‘datadir’

Hope it helps


Recover deleted data SQL Server On 03/19 one user had deleted the data from 3 tables from one of the databases that we have. Now we have the full backup of that database each day. What we want to do is to restore the deleted data and then get all the data entered after 03/19 as well for those tables. Any suggestion on how to do that?

Any suggestion on how to do that?

The Easiest way to recover the data (in your scenario) using third party tool
which help you to read transaction log (backups or online),  and able to generate (T-SQL) script that can recover particular data effected by delete operation.

In case if the recovery model is simple, database has to be set OFFLINE or detached for making live database files connection to do the recovery, since the LOG Backup cannot be possible in simple recovery mode. However, Chances are less (in simple recovery) if the duration between recovery action and deleted operation is too long as the log truncation happens automatically.


How do you select non-valid column names used by views / functions and SPs? In trying to setup replication I'm finding that some of the objects (views and SPs) are outdated but were not removed from the database. So I'm getting errors along the lines of:

Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164)
Get help: http://help/MSSQL_REPL20164

Invalid column name 'iNoOfUnits'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207

Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL)
Get help: http://help/MSSQL_REPL

Which means i have to open up every view and function (and there are hundreds) to manually search for usage of a particular column (or possibly table) name.

Is there a way I can list all objects in a database and column references that these objects use?

I have seen this question: How to find all views with invalid column names. But I don't think it's returning columns that aren't present on the database.

But I'm wondering if there isn't a better way? How are SPs/Views/etc stored in the database? Is it text? then I would be able to select the text that contains columns that aren't present any more...

I wish the log contained the name of the object that is trying to reference an illegal column name...

===== ANSWER

The easiest way I have found, thanks to the accepted answer below, is to open an SSDT project and import the database. Then search through the solution for columns that cause subscription errors. And fix one-by-one...

In practice, you can't because dynamic SQL is effectively impossible to predict - you can't predict what string is going to be built before it runs.
Even if you try, queries can have branching logic - for example, based on parameters, you may build different SQL.


How to relate 2 tables to a third when the first 2 tables have a relationship? For once, I find myself at an utter loss in terms of coming up with a good question title.

Consider the three following tables. Each has additional columns, but only the columns relevant to the relationships between the tables are mentioned:

Table a has a primary key a_id
Table b has a primary key b_id and a column a_id which is a foreign key to a (one-to-many relationship)
Table reference has a primary key ref_id

reference is a list of sources that are cited to substantiate information contained in the other 2 tables. As such, I need tables to relate them together. Both a and b have many-to-many relationships with reference, but there is an additional complication: a footnote number. This footnote number must be unique for each a and reference pair, but it is only required when a relationship between a b and the same reference exists. (Of course, that b must be related to the same a.) The best way I have come up with to represent this relationship so far is a pair of tables:

bridge_a_reference:

Columns: a_id, ref_id, footnote_num (nullable)
Primary key: a_id, ref_id
Foreign keys: a_id to table a; ref_id to table reference

bridge_b_reference:

Columns: b_id, a_id, ref_id
Primary key: b_id, ref_id
Foreign keys: b_id, a_id to table b; a_id, ref_id to table bridge_a_reference

This ensures data consistency. There can only be a relationship between b and reference if a corresponding relationship between a and reference exists and b is related to the correct a. It also ensures that the footnote number will be consistent for all a/reference pairs. However, it's redundant, storing the relationship between a and b a second time (though enforced to be consistent, at least), and it doesn't ensure the footnote number is required for the b/reference relationships.

I've tried rearranging the tables in my head a few times, but everything else I can come up allows some kind of inconsistency (b to reference relationships without the corresponding a to reference or b to a relationship or inconsistent footnote numbers for the a/reference pairs.) How can I better structure my bridge tables to eliminate redundancy and ensure the footnote requirements? Is it even possible?

Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)

We remove footnote_num from:

Table:      a_ref         -- was named:  bridge_a_reference
     a_id,
     ref_id
Primary key:
     (a_id, ref_id)
Foreign keys:
     a_id  -> a
     ref_id -> reference

We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:

Table:      a_ref_with_footnote
    a_id,
    ref_id,
    footnote_num
Primary key:
    (a_id, ref_id)
Unique key:
    (a_id, footnote_num)
Foreign keys:
    (a_id, ref_id)  -> a_ref

And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):

 Table:     b_ref         -- was named: bridge_b_reference:
     a_id,
     b_id,
     ref_id,
 Primary key:
     (b_id, ref_id)
 Foreign keys:
     (a_id, b_id) -> b
     (a_id, ref_id) -> a_ref_with_footnote


In the domain of SQL Server backup, what is a backup set, backup device, backup media, backup media set, backup media family I'm not a native English speaker and I've read the descriptions in BOL but still can't understand what exactly are they. Can anyone explain in an easy understand way?

Thanks.

The screenshots from SSMS will help you understand the terms:

backup set - is a backup from a single, successful backup operation. E.g. a full database backup created at 8 AM yesterday

You can see that there are 2 backup sets in a single backup media set

backup device - a disk file or tape drive

backup media - tapes or disks where backup are stored
backup media set - in the screenshot - it's the BAK file where backups are added

backup media family - You're creating the 8 AM full database backup and your database is huge, the backup takes more space than 4 tapes can store. Say you have 4 tape devices that are used to backup your database. When one tape is full, you insert a new one. If your database backup takes 20 tapes, that's 5 tapes per each tape device. The 5 tapes created on the tape device 1 belong to the same backup media family, as they have the same "father" - tape device 1


Clustered index always better than Non-Clustered index? When I have a clustered index on 'A', is it better to use this clustered index even if I have

selection of 'A=constant'? If not, why?

Thank you!

I just want to put in a word of warning: please very carefully pick your clustered index!

It's the most replicated data structure in your SQL Server database (assuming it's SQL Server you're talking about). The clustering key will be part of each and every non-clustered index on your table, too - certainly in the leaf level, possibly also in the index navigation structure.

You should use extreme care when picking a clustering key - it should be:

narrow (4 bytes ideal)
unique (it's the "row pointer" after all - if you don't make it unique, SQL Server will - for you - in the background - costs your a couple of bytes for each entry - times the number of rows and the number of nonclustered indices you have - can be very costly!)
static (never change - if possible)
ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)
it should be non-nullable and ideally also fixed with - a varchar(250) makes a very poor clustering key

Anything else should really be second and third level of importance behind these points ....

See some of Kimberly Tripp's (The Queen of Indexing) blog posts on the topic - anything she has written in her blog is absolutely invaluable - read it, digest it - live by it!

GUIDs as PRIMARY KEYs and/or the clustering key
The Clustered Index Debate Continues...
Ever-increasing clustering key - the Clustered Index Debate..........again!


Which schema is better? I have three entities: Document, Asset, and Client

A Document can be linked to several assets. An Asset can have many documents.

A Document can be linked to several clients. A Client can have many documents.

Is it better to have two join tables...

asset_document
    asset_id
    document_id
    ...more fields

client_document
    client_id
    document_id
    ...more fields (same fields as in asset_document)

Or just one join table?

document_relationships
    document_id
    object_id (could be a client_id or asset_id)
    type (whether it's a client or asset)
    ...more fields

My guess is that the first one is better but I can't explain why. I'm looking for a strong argument against the second one. My co-developer prefers the second one to reduce duplication and lesser tables to maintain and lesser tables to join in queries.

Take note their could be more entities where Document will be linked to but ...more fields will stay the same.

You should definitely two separate tables!

What if, in the future, documents get linked to something else?

As it is anyway with your documents_relationship table, you are most likely going to have fields that apply to clients and not assets and vice versa!

This will mean NULLs. Good frule of thumb IMHO, a field should never be NULL by design for its entire life. Note: this does not mean that NULLs should never be used!


Decompostion with loss of information shown by junction F={A → B; B → C; C → D}

R is decomposed in R¹(A,B) & R²(A,D) & R³(C, D)

Is it without loss of information?

I would have said yes as far as with the following array

A → B x²²= b

B → C x¹³=x²³=c

Therfore we should see that it is without loss of information (we should have a straight line of determinated data). However, the answer says that it clearly lacks informations and in order to show that it is with loss of information, one should find an instance r such that

r ≠ r¹⋈ r²⋈ r³

I know that I have then to do some arrays and some junctions but I don't even know how to start the arrays...

The trick to solve this exercise it to repeat as much as possible the elements of the table. Since A is the key, you cannot repeat A, but you can, for instance, repeat two times B and C and three times D. Here is a very simple and short example (note that the functional dependencies are respected):

r =
+---+---+---+---+
| A | B | C | D |
+---+---+---+---+
|a1 |b1 |c1 |d1 |
|a2 |b1 |c1 |d1 |
|a3 |b2 |c2 |d1 |
+---+---+---+---+

And since this is an exercise, I will leave to you the task of projecting r over R1, R2 and R3, and discovering how the natural join of the three relations differs from the original relation r.

Finally a terminological note: a relation instance is not an array! There is no order in the rows or the columns of a relation, while in an array the order is essential.


SSL error during SQL Server install During my SQL Server 2014 Enterprise installation efforts, I encountered an error near the end of the install that has prevented completion.

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.).

There is currently no cert on the server so it looks as though SQL Server has generated a self signed cert to connect with, but isn't being trusted.

Note that all this is happening before the install is complete, so configuration manager isn't available yet. My thought is to turn encryption off, but with no config manager, I am not sure where there may be a config file for the install somewhere that I can edit. Or maybe I would need to put a trusted cert on the server, but without configuration manager fully available, there isn't a way to tell SQL Server to use this the trust cert.

Tom Li- MSFT described a solution to what sounds like your problem, but his solution was for SQL Server 2008.  (But just how much has the installer been improved since then?)

https://social.msdn.microsoft.com/forums/sqlserver/en-US/f009e660-503a-4569-a92d-9cc2ef790ca3/install-failure-certificate-chain-trust

Key points from his post include:

This certificate is used as a mechanism to protect SQL Server's login information(including password) when using SQL authentication.

By default this self-signed certificate should only affect the login information and only when using SQL authentication.

Edit: My certificate name usually matches the SQL Server's login account.
Export the certificate and delete it by using "certmgr.msc". Then run "setup.exe" to install SQL Server 2008 again.

You also can add this certificate to the Trusted Root Certification Authorities store to solve this issue.
In order to do this, please execute:

Certutil -Addstore -f Root <File.Cer>
Run "cliconfg.exe" from a Windows run dialog to determine that you did not check "Force protocol encryption".


ORA-12705: Cannot access NLS data files or invalid environment specified with jmeter test I have trying to execute a simple test with jmeter in Oracle 19.7 database. This is my lang configuration in database
SQL> select * from nls_database_parameters;
...
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_CHARACTERSET    AL32UTF8
NLS_TERRITORY   AMERICA
NLS_LANGUAGE    AMERICAN

In my linux machine I have this variables
$ export LANG=en_us
$ echo $LANG
en_us
$ echo $NLS_LANG
American_America.UTF8

and I execute this test
$ ./jmeter -n -t ../prueba_select_jdbc.jmx -l /tmp/resultado -e -o /tmp/prueba_jmeter -Jthreads=10 -Jloop=5 -Duser.country=US -Duser.language=en
    Creating summariser <summary>
    Created the tree successfully using ../prueba_select_jdbc.jmx
    Starting standalone test @ Mon Nov 30 19:51:43 CET 2020 (1606762303758)
    Waiting for possible Shutdown/StopTestNow/HeapDump/ThreadDump message on port 4445
    summary =     50 in 00:00:09 =    5.4/s Avg:    51 Min:    29 Max:   556 Err:    50 (100.00%)
    Tidying up ...    @ Mon Nov 30 19:51:53 CET 2020 (1606762313390)
    ... end of run
$

and the error that i allways get is
null 0/java.sql.SQLException: Cannot create PoolableConnectionFactory (ORA-00604: error occurred at recursive SQL level 1\nORA-12705: Cannot access NLS data files or invalid environment specified\n)

I have tried multiple conbinations but none of then works.
Regards
Edit 1:
I have configured instant client and I used sqldeveloper launch from here without problems.
$ env |grep ORA
ORACLE_HOME=/usr/lib/oracle/19.3/client64
$ set |grep LIB
LD_LIBRARY_PATH=/usr/lib/oracle/19.3/client64/lib
$ echo $PATH
...:/usr/lib/oracle/19.3/client64/bin

As per Oracle FAQ

Setting the NLS_LANG environment parameter is the simplest way to specify locale behavior for Oracle software. It sets the language and territory used by the client application and the database server. It also indicates the client's character set, which corresponds to the character set for data to be entered or displayed by a client program.

So you can try explicitly setting user.country and user.language Java system properties, either add the next lines to system.properties file (lives under "bin" folder of your JMeter installation:
user.country=us
user.language=en

or pass these properties via -D command-line arguments like:
./jmeter -Duser.country=us -Duser.language=en -n -t .....

References:

How to change the LOCALE (Language and Country) and TIMEZONE for the IBM Technology for Java (IT4J) VM on the IBM i
Configuring JMeter
Apache JMeter Properties Customization Guide


Depending on how many values you have for the 'foo' column, you may be able to accomplish this with a union:

select   foo, sum(position)/count(position) as average_position
from     (
    select   foo, position
    from     my_table
    where    foo = 'bar1'
    order by date desc limit 3
union all
    select   foo, position
    from     my_table
    where    foo = 'bar2'
    order by date desc limit 3
) as subquery
group by foo;


Get streak count and streak type from win-loss-tie data I made a SQL Fiddle for this question if that makes things easier for anyone.

I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data (like 'W2' if the team has won their last 2 matchups, or 'L1' if they lost their last matchup after winning the previous matchup - or 'T1' if they tied their most recent matchup).

Here is my basic schema:

CREATE TABLE FantasyTeams (
  team_id BIGINT NOT NULL
)

CREATE TABLE FantasyMatches(
    match_id BIGINT NOT NULL,
    home_fantasy_team_id BIGINT NOT NULL,
    away_fantasy_team_id BIGINT NOT NULL,
    fantasy_season_id BIGINT NOT NULL,
    fantasy_league_id BIGINT NOT NULL,
    fantasy_week_id BIGINT NOT NULL,
    winning_team_id BIGINT NULL
)

A value of NULL in the winning_team_id column indicates a tie for that match.

Here's a sample DML statement with some sample data for 6 teams and 3 weeks worth of matchups:

INSERT INTO FantasyTeams
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6

INSERT INTO FantasyMatches
SELECT 1, 2, 1, 2, 4, 44, 2
UNION
SELECT 2, 5, 4, 2, 4, 44, 5
UNION
SELECT 3, 6, 3, 2, 4, 44, 3
UNION
SELECT 4, 2, 4, 2, 4, 45, 2
UNION
SELECT 5, 3, 1, 2, 4, 45, 3
UNION
SELECT 6, 6, 5, 2, 4, 45, 6
UNION
SELECT 7, 2, 6, 2, 4, 46, 2
UNION
SELECT 8, 3, 5, 2, 4, 46, 3
UNION
SELECT 9, 4, 1, 2, 4, 46, NULL

GO

Here is an example of the desired output (based on the DML above) that I'm having trouble even beginning to figure out how to derive:

| TEAM_ID | STEAK_TYPE | STREAK_COUNT |
|---------|------------|--------------|
|       1 |          T |            1 |
|       2 |          W |            3 |
|       3 |          W |            3 |
|       4 |          T |            1 |
|       5 |          L |            2 |
|       6 |          L |            1 |

I've tried various methods using subqueries and CTE's but I can't put it together. I'd like to avoid using a cursor as I could have a large dataset to run this against in the future. I feel like there might be a way involving table variables that join this data to itself somehow but I'm still working on it.

Additional Info: There could be a varying number of teams (any even number between 6 and 10) and the total matchups will increase by 1 for each team every week. Any ideas on how I should do this?

Since you are on SQL Server 2012 you can use a couple of the new windowing functions.

with C1 as
(
  select T.team_id,
         case
           when M.winning_team_id is null then 'T'
           when M.winning_team_id = T.team_id then 'W'
           else 'L'
         end as streak_type,
         M.match_id
  from FantasyMatches as M
    cross apply (values(M.home_fantasy_team_id),
                       (M.away_fantasy_team_id)) as T(team_id)
), C2 as
(
  select C1.team_id,
         C1.streak_type,
         C1.match_id,
         lag(C1.streak_type, 1, C1.streak_type)
           over(partition by C1.team_id
                order by C1.match_id desc) as lag_streak_type
  from C1
), C3 as
(
  select C2.team_id,
         C2.streak_type,
         sum(case when C2.lag_streak_type = C2.streak_type then 0 else 1 end)
           over(partition by C2.team_id
                order by C2.match_id desc rows unbounded preceding) as streak_sum
  from C2
)
select C3.team_id,
       C3.streak_type,
       count(*) as streak_count
from C3
where C3.streak_sum = 0
group by C3.team_id,
         C3.streak_type
order by C3.team_id;

SQL Fiddle

C1 calculates the streak_type for each team and match.

C2 finds the previous streak_type ordered by match_id desc.

C3 generates a running sum streak_sum ordered by match_id desc keeping a 0 a long as the streak_type is the same as the last value.

Main query sums up the streaks where streak_sum is 0.


Postgres copy data with \xYY as plain string instead of interpreting as encoded string I have a log file full of URLs, generated by Bro IDS.

When Bro logs a URL with non-ascii characters in it, it inserts \xYY where YY is the hexadecimal character code. Also, some URLs contain "\x".

Is there a setting or flag I can use with the COPY or \copy command to stop postgres from trying to interpret these string sequences and just put them in the text field as is?

The backslash \ is the default escape character in the (default) text format of COPY. The format you describe is recognized as (quoting the manual here)

  backslash sequences are recognized by COPY FROM
  ...
  \xdigits | Backslash x followed by one or two hex digits specifies the character with that numeric code

.. which seems to be just as intended.
To avoid this effect you could use the CSV format instead, where the backslash has no special meaning:

COPY tbl FROM '/path/to/file/log.sql' (FORMAT csv);

Be sure to read the manual to learn about additional differences.

Alternatively you could replace every \ with \\ in your file with a sed script or something.

If the above doesn't solve your problem, please supply:

PostgreSQL version
Database encoding
Demo data
Definition of the table you COPY to
The exact COPY invocation you used


MySQL server performance plummets Part of our software solution is responsible for processing files that get uploaded to us by our clients. We want to process them as quickly as we can.

Our system is architected so that there's a single redis job queue, and the processors live on separate processing servers. We can turn a processing server on/off individually. Ideally, the more processing servers we have turned on, the faster we are able to process uploaded files.

The entries in the redis job queue point to rows in MySQL. In order to actually do the processing on the uploaded files, we need to read from and write to MySQL.

The two graphs below show how our system performance changes as we add more processing servers. The green lines on both graphs show the number of processors running. Notice that as we add new processors, the rate at which we process files increases for a while, levels off and then decreases. The average select query time steadily grows, and then explodes. (Ignore the spike in select time around 11:04.)

I feel pretty confident that MySQL is reaching a performance maximum. However, I am not a MySQL admin, nor do I play one on TV.

Do you all have any suggestions about how to track down this performance issue? I can tell you that there are no queries in the slow log; that the machine has 24 cores, and each time I add a new processing server to the mix, the CPU % on mysql goes up by about 100%.

there are no queries in the slow log

Then, make sure they will be. One of the easiest ways to profile MySQL queries is through the slow log. if you find no slow queries is because you probably have a lot of them that take lower that 10 seconds to execute, or that are locked waiting (that time is not considered, "execution time"). Set long_query_time to a lower value, and if you want to profile fully the server, you can temporarily set it to 0 (Warning: may have a bin impact on your query performance), to log all your queries.

That, combined with aggregation tools like pt-query-digest is the 101 of server query optimization. There are other monitoring tools and techniques, but this is a good place to start. You may not identify 100% of the problems, but 90% in my experience.

  I feel pretty confident that MySQL is reaching a performance maximum.

That is an assumption without data supporting it. Get evidence first (a.k.a. profile), then evaluate options.

  each time [...]CPU % on mysql goes up by about 100%

That is not necessarily a bad thing. Resources are being used. The question is if they are doing it efficiently (that is the real question).


How to Set Up Sync of two mysql databases without master slave The purpose is to create the live database replication for staging server but with masking of personal identifiable information like email, mobile, passwords etc.

This has to be done once daily, not real time like master slave. Also, when the new rows inserted, they all should be masked like previous ones before insertion.

One way i found is using the binary logs of the mysql, but they can be inserted first, and then masked, which is not required.

Any other suggestion?

Database size is more than 40gb

You can use any of the available ETL Tools:

Talend
Pentaho
others, like Informatica

Initial size not important, the most important is the daily number of changes.

You will need to add a timestamp for INSERT/UPDATE or a bit column and triggers to mark the new sync status.

My clients sync millions-row databases (DB2/MySQL/Web) by cron jobs.

An other solution may be SymmetricDS (read the article about transformation of data using it: http://www.symmetricds.org/doc/3.0/html-single/user-guide.html#transform-data).


How can I get Sql Server 2016 on a 32-bit server? I didn't build it, but we've got a 32-bit Windows Server 2008, with SQL server 2008 and 4GB of RAM.

I need to upgrade it to SQL Server 2016 in order to log ship to a new server, so we can put this thing out of its misery.

The problem - it looks like SQL Server 2016 requires, at minimum, Windows Server 2012, which is only 64-bit.

Question: how can I migrate the data to a SQL 2016 server with <1 day of downtime?

Is there a way to get around it with compatibility levels?

How can I get Sql Server 2016 on a 32-bit server?

You can't, SQL Server 2016 only comes compiled for 64-bit.

  Is there a way to get around it with compatibility levels?

Thankfully, no.

  I need to upgrade it to SQL Server 2016 in order to log ship to a new server, so we can put this thing out of its misery.

You'll need to have someone install an OS supported by SQL Server 2016, then install SQL server 2016.

  Question: how can I migrate the data to a SQL 2016 server with <1 day of downtime?

Install a supported OS for SQL Server 2016 on the old 2008 server. Install SQL Server 2016, then setup log shipping as you normally would per your above quote.


Select the students that are enrolled in both the courses This is the table definition. I want to select the students that are enrolled in courses (1 and 3).

From this dataset the expected output is 'B'.

You can approach this problem using conditional aggregation based filtering. Firstly, get all the students who are either enrolled IN(..) course no (1, 3).

Then, you can use HAVING clause to filter out the students having atleast two unique courses subscribed to (which would be 1 and 3 in this case):

SELECT student_name
FROM your_table_name
WHERE course_number IN (1,3)
GROUP BY student_name
HAVING COUNT(DISTINCT course_number) = 2

For good performance, you can define the following Covering Index: (course_number, student_name)


Does a SQL Server update nvarchar statement overwrite the same address on disk if the new value is the same size? Is it possible to overwrite the same disk address in a T-SQL update statement? A use case would be preventing recovery of the original text on disk for security or anonymity reasons.
CREATE TABLE Test(
  Id int,
  Message nvarchar(1000))
GO

INSERT INTO Test (Id, Message) VALUES (1, 'Hello!')
GO

So at this point, 'Hello!' is written to disk. If an update statement is run, can it be guaranteed (or at least highly likely) to overwrite the same location on disk?
UPDATE Test SET Message = '000000' WHERE Id = 1
GO

In the SQL Server implementation, what is the probability that the original 'Hello!' value will no longer be on disk?
I'm assuming the new value would be the same size. My guess is that if the new value were of a different size, then SQL Server would write the updated value to a new location, leaving the 'Hello!' in the original location, but now marked as free. I also assume that a delete does nothing to the original value on disk, but just marks the location as free.
If this is not the case, or not guaranteed, or at least highly-likely, Is there another way to remove the value from disk?
The requirement here is not to the level of keeping government secrets safe. It's more of a marketing claim that deleted items are truly gone and can't be recovered.
I understand there are a lot of caveats here. I know the SQL language does not address this. I'm asking specifically about SQL Server's implementation. The answer could be that it's unknowable. Just wondering if there's documentation, or common knowledge, or if someone has tested implementation specifics.
We're using a hosting service that doesn't encrypt the SQL Server disk. We do encrypt the value before writing to the db. Just looking into a 'belt and braces' solution.

There are so many aspects to this, but the short answer is that you have to expect that old values can be on disk (as this is from what I understand what the question boils down to).
So, this falls under the same category as below question, with the same answer:
Q: Can we protect from a Windows admin to be able to do anything inside a SQL Server and see all data in a SQL Server?
A: No. But you can try to make sure that what they read appear as garbage to them. I.e., a suitable encryption implementation.
If you are truly interested in this, I suggest you first read up on the storage architecture of SQL Server. Without that, you won't be able to make heads of tails of the answers. I.e., make sure you understand database files, pages, transaction logging, heaps, B-trees etc.
The update can be done in place, on the same page. That goes for an index as well. If you modify the key of an index column then the row has to move (DELETE/INSERT internally). If the row doesn't fit, then you get a forwarded record or a page split. Another factor is that after a delete, (and an update can be delete/insert, remember), the old row will initially still be there as a ghost row. But even after that, the page isn't "compacted on delete", i.e., the storage reclaim is done when the "free" space is needed at some later time.
And then you have transaction logging.
And backups.
If you have versioning enabled, then old row versions are in tempdb and cleared up when not needed, after some time.
And the added complexity how other, perhaps less basic, technologies affects the picture. I'm thinking of things such as:

Columnstore indexes
Memory optimized tables
Change Data Capture
Replication
HA solutions such as Availability Groups
And whatever more I could come up with given some more time to think about this

I think you get the picture by now. No, you can't claim that the old data is gone. Encryption might be an important component to this, depending on your particular situation.


Why can't I double quote built-in type names in Postgres? Why can't I double quote the primary built-in type names in Postgres despite being able to double quote their aliases?  For example, these work fine

SELECT CAST (1 as bool);
SELECT CAST (1 as "bool");
SELECT CAST (1 as boolean);
SELECT CAST (1 as int8);
SELECT CAST (1 as "int8");
SELECT CAST (1 as bigint);

but these fail

SELECT CAST (1 as "boolean");
SELECT CAST (1 as "bigint");

(See http://www.sqlfiddle.com/#!17/a6ad3/13)

You can only quote identifiers, but not keywords. Data types prescribed by the SQL standard, such as int, integer, boolean, bigint etc. are keywords and, when put in quotes, are not recognised as such, producing errors. bool and int8, on the contrary, are, as you say, Postgres-specific aliases and are therefore identifiers, which allows them to be quoted.

Note that aliases follow the rules for quoted identifiers:

SELECT CAST (1 as BOOL); -- succeeds
SELECT CAST (1 as "BOOL"); -- fails

A little explanation of how the parser works.

  A token can be a key word, an identifier, a quoted identifier, a literal (or constant)

The order of precedence in the quote above is significant. When seeing an unquoted sequence of characters, the parser converts it to lowercase and first tries to match it against the known keywords. If unsuccessful, it will then try to find an identifier with that name. When seeing a quoted character sequence the parser knows that this must be an identifiers (since keywords are never quoted).

So,

BOOLEAN --> to lowercase --> boolean --> is keyword? yes --> done.
BOOL --> to lowercase --> bool --> is keyword? no --> is identifier? yes --> alias for boolean --> done.
"BOOL" --> quoted identifier, don't change case --> does not exist --> done.


Nullable One To Many w/Join Table - Indexing I have a question regarding database design, I am working with PostgreSQL using Hibernate for ORM, the design I inherited includes the following tables

users

user_id (pk)
user_name
user_dob ...

sessions

session_id (pk)
session_date
session_duration ...

user_sessions

user_id (fk)
session_id (fk)

As you can guess one user can have many sessions, the reason (I believe) the join table was used is because a session can be added before the user exists (and it is linked to the user after they signup), thus avoiding a nullable field in the sessions table...fine.

But I have recently been looking at creating some multi-column indexes to speed up certain queries and have realised that I cannot include the user in this index as the column is in a different table.

As I am fairly new to DB design I am just wondering if the above design is correct? Or am I in fact better off using a nullable FK in the session table to allow me to index the user relationship?

If a session can only belong to a single user at any given time, the more appropriate model would be without a join table (user_sessions). What's the problem with column user_id being NULL?

If you need it to be NOT NULL and you don't always know the user at creation time, just insert a user "unknown" (with user_id 0 in my example). Can serve as DEFAULT value, too ...

CREATE TABLE sessions
  session_id   serial PRIMARY KEY
 ,user_id      int NOT NULL DEFAULT 0 REFERENCES users(user_id)
 ,session_date timestamp
 ...
)

Note on indexing strategy

Postgres can combine multiple indices with excellent speed. Typically, in your original scenario, with a nested loop:

  nested loop join: The right relation is scanned once for every row found in the left relation. This strategy is easy to implement but can
  be very time consuming. (However, if the right relation can be scanned
  with an index scan, this can be a good strategy. It is possible to use
  values from the current row of the left relation as keys for the index
  scan of the right.)

Say, you have these indices:

sesssions(session_id)
user_sessions(session_id, user_id)

Both should be covered by the pk constraints in your original model automatically. However, the second one could go either way (user_id, session_id) or (session_id, user_id) and that matters:
Is a composite index also good for queries on the first field?

For best performance in a query like:

SELECT u.user_name, s.session_date
FROM   users         u
JOIN   user_sessions us USING (user_id)
JOIN   sessions      s  USING (session_id)
WHERE  u.user_name = 'foo'

You would need the index (or pk constraint) to be

user_sessions(user_id, session_id)

Or have an additional index with user_id as first column.


How do I check how many rows a query will affect before executing it? I'm writing an application to interface with my database (SQL Server 2014 SP1 Express), and there are two sections of the application that worry me slightly. The first involves an UPDATE:

UPDATE Jobs
SET JobStatus = 'Running' --there's also an UPDATE where this is set to 'Completed'
WHERE (ExperimentID = @currentID);

And a DELETE:

DELETE from Simulations
WHERE (ExperimentID = @currentID);

These seem like fine statements at a glance, but if there's something wrong with how @currentID is passed from my application to the DB, I could possibly lose all information about which Jobs have been run, and more gravely, all of my simulation data.

How do I protect these queries from deleting/updating more than @n rows? The UPDATE should really only update one row at a time, so I'd like that to be a restriction. There's also a column in the Simulations table that could be used to restrict how many the DELETE should be getting rid of, by doing:

SELECT MAX(CycleCount)+1 AS numRows
FROM Simulations
WHERE ExperimentID = @currentID

So what do you think? How best do I sanitize these queries to prevent them from doing damage in the event that my application fails?

There are three tables in this DB: Jobs, Parameters, and Simulations. ExperimentID is a primary key for both Jobs and Parameters, and they are both the same size (each job has a corresponding set of parameters). There's more detail about Parameters in my post history if you're interested.

The Simulations Table stores results from a simulation at each step of the simulation. So the primary key is then formed not just by ExperimentID (as theres many many steps per simulation), but by the compound of ExperimentID and CycleCount.

I'm trying to account for situations where something gets corrupted in transfer (highly unlikely, but you never know), and situations where a simulation terminates prematurely (loses connection with DB, computer loses power, etc). ExperimentID in both Jobs and Parameters is determined by IDENTITY(1,1). Hope this clears up a bit of what I'm hoping to accomplish here.

@currentID is programmed as an integer. It is pulled initially from the database using:

SELECT MIN(ExperimentID) FROM Jobs WHERE JobStatus = 'ToRun'

If ExperimentID is the primary key or has unique constraint/index, there is no need to limit the number of rows updated since there will never be more than one row updated.  If ExperimentID is not unique by itself, I suggest you add additional criteria to identify the desired row.  The TOP approach you provided will limit the number of rows updated for non-unique values but the one updated will be arbitrary unless your order by additional columns such that the updated row deterministic.

To validate no more than the expected number of rows are modified, one method is to perform the DML in a transaction and rollback if the number of rows is greater than expected.  Below is a parameterized example of this technique:

SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRAN;

    UPDATE Jobs
    SET JobStatus = @NewJobStatus
    WHERE ExperimentID = @currentID;

    DECLARE @RowCount int = @@ROWCOUNT;

    IF @RowCount > @MaxRowCount
    BEGIN
        RAISERROR ('Attempt to update %d rows, more than the %d row(s) allowed.  Action not performed.', 16, 1, @RowCount, @MaxRowCount);
    END;

    COMMIT;

END TRY
BEGIN CATCH

        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;

END CATCH;


Could not start mysql or MariaDB. Permissions were ok, but mysql-bin.index permission error Using MariaDB 5.5.36. Here's what I got while trying to start MariaDB:

# syslog
Dec 14 09:40:52 vps1497 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Dec 14 09:40:52 vps1497 mysqld: 141214  9:40:52 [ERROR] mysqld: File '/var/log/mysql/mariadb-bin.index' not found (Errcode: 13)
Dec 14 09:40:52 vps1497 mysqld: 141214  9:40:52 [ERROR] Aborting
Dec 14 09:40:52 vps1497 mysqld:
Dec 14 09:40:52 vps1497 mysqld: 141214  9:40:52 [Note] /usr/sbin/mysqld: Shutdown complete

# ls -alh /var/log/mysql/
-rw-rw----  1 mysql adm    9023947 Dec  8 03:01 mariadb-bin.002905
-rw-rw----  1 mysql adm       2550 Dec  7 03:01 mariadb-bin.index

Permissions seem fine, trying to start mysql with root doesn't work. Any clue?

You need to make /var/log/mysql fully owned by mysql

chown -R mysql:mysql /var/log/mysql

Then go start MariaDB

Make sure you are logged in as abuser that can start MariaDB

If not, use sudo

sudo service mysql start


Update using Sub Select SQLServer I have a table i'm trying to update, where i'm adding a prefix to some records, depending on whether or not, the value appears in another table.
For example, I have a region_ref, and region_no, and I want to update all of the regions, if the region_no exists in another table.

The problem is, if i were to update all records, it would fail on some, due to the column length. However, i'm not updating these records as they dont appear in the sub select (these records dont appear in the sub select if i run it standalong.

update emp
set region_ref = 'UK_' + emp.REGION_REF
from EMPLOYEES emp
inner join REGIONS reg
on emp.region_no=reg.REGION_NO
where emp.region_no in (select region_no from UKREGIONS);

There are two records in the employees table which are already at the max value, which I shouldnt be updating anyway. Any ideas? Its almost like the update statement is being validated without the sub select.

As a workaround, try using the RIGHT function to avoid the length overflow.

Just drop whatever the max length of your (I'm assuming) CHAR column in the query where I made a note.

update emp
set region_ref = RIGHT('UK_' + emp.REGION_REF , /*put the max length of your column here*/)
from EMPLOYEES emp
inner join REGIONS reg
on emp.region_no=reg.REGION_NO
where emp.region_no in (select region_no from UKREGIONS);


Execute privilege granted successfully, but I can't execute the procedure I am using the database as me.

The procedure exists as an object that belongs to me.

Grant sucessful.

But when I attempt to EXECUTE insert_into_purified; i get
Error starting at line : 469 in command -
BEGIN insert_into_purified; END;
Error report -
ORA-01031: insufficient privileges
ORA-06512: at "C##ELLIE.INSERT_INTO_PURIFIED", line 5
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

It seems the user lacked CREATE VIEW privilege.
I can reproduce the error, but not the part about needing to grant it to yourself, which doesn't make sense. If you could grant privileges to yourself there would be no security at all
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Connected as sys AS SYSDBA

SQL> create user ellie identified by ellie quota unlimited on users;

User created

SQL> grant create session, create table, create procedure to ellie;

Grant succeeded

SQL> conn ellie/ellie
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Connected as ellie

SQL> create or replace procedure insert_into_purified as
  2  begin
  3      execute immediate 'create or replace view demo_view as select * from dual';
  4  end insert_into_purified;
  5  /

Procedure created

SQL> exec insert_into_purified
begin insert_into_purified; end;

ORA-01031: insufficient privileges
ORA-06512: at "ELLIE.INSERT_INTO_PURIFIED", line 3
ORA-06512: at line 1

SQL> conn / as sysdba
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Connected as sys AS SYSDBA

SQL> grant create view to ellie;

Grant succeeded

SQL> conn ellie/ellie
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Connected as ellie

SQL> exec insert_into_purified

PL/SQL procedure successfully completed

SQL> desc demo_view
Name  Type        Nullable Default Comments
----- ----------- -------- ------- --------
DUMMY VARCHAR2(1) Y


Monthly backup of SQL server DB to PostgreSQL? The company I'm working for has a SQL Server with read-only access. They use Crystal Reports hooked up to PostgreSQL for reporting. Is there any way to make it so I can move all the data from the MSSQL DB to PostgreSQL without user interaction? That seems to be the caveat to what I'm trying to do. They need to be able to run this report after I leave without having to interact with it during the process.

Or am I looking at this the wrong way? Is there a way to save a "snapshot" of the SQL Server DB that can be manipulated in Crystal Reports? The ultimate goal is that since the DB is dynamic we need to be able to have a static DB at the end of the month that all the reports can be ran on without having to worry about it changing.

Can you not just hook Crystal Reports to an instance of MS SQL Server? That way the process should be as simple as "backup the live database, restore it into test/snapshot location when needed".

If the database is less than 10Gb in size you can use SQL Express so there would be no licensing implications, if it is 10Gb or more than you could restore it on the same SQL Server under a different name assuming you have access to be able to do that. Otherwise you might end up paying for an instance of SQL Server (avoiding this may be why you are using postgres in the first place).

If you have a specific process that you are currently following manually which you would like to automate, then you need to specify that process in detail in your question otherwise we can only guess and that may not be helpful.


When do you know that you've made a design flaw in a SQL/relational database? I've been working with a a relational model for a work project over the last few months, and I've gotten to the point where I'm wondering if I've made a mistake in my model's design or implemented things incorrectly. I can't go into specifics about the model because it's for work, but I'll try my best to elaborate.

The database contains data from three separate sources that are time sensitive. I.e., all of the date starts out with a DATE or DATETIME of some form. I've written a process to aggregate that data to unique values over date ranges and then run those unique values through an optimization engine in order to gain insights about an "aggregated optimal view" of the data. The process seems to work fine when there are relatively small amount of data (think ~170,000 records per date range that I select) but doesn't seem to be scaling upwards at all. Some of the queries that I've written are now taking much longer than they used to, and I think it's because of the way that I've designed the system.

Here are some of the "symptoms" of my database:

I use indexes in my tables, but haven't defined foreign key relationships

I was under the impression that have well defined indexes for each of my JOINs was sufficient

There are some queries where I have as many as 4 or 5 nested subqueries
Some of my queries JOIN 6 or 7 tables together in order to get data
A couple of queries that I have are >300 LOC

I could add a few more, but I think that this gets my idea across.

My question is: when does one know that their database isn't modeled properly?

Is it when your queries stop scaling? When you have to write long queries to get the data that you're looking for?

I can give more information about the stack and machine that I'm using if it helps.

Thanks

Modeling and performance are related but not quite the same thing.  Performance and scalability will have a lot to do with what DBMS you are actually using.

Queries which seem to be slow and difficult to execute against MySQL might fly when run against Postgres for instance.  This has everything to do with how intelligent the query planner is.  Older versions of MySQL for instance only seem to know how to perform one kind of join:  Nested-Loop.  This will be fast for small amounts of data and simple queries, but can degrade quickly for complex queries.

You may not have a design flaw.  You may have simply hit the performance wall with your current DBMS.  Which database are you using?

In my opinion, an error in the model exists when you have insert, update, or delete anomalies in the data.  Imagine you store a customer name on each invoice and the customer changes their name.  To perform this update should not require updating the history of every order the customer has ever made.  If it does, you've got a problem with your model.


Impact on SSRS of migration of SQL Server from 2008 to 2012 As part of my project, we are upgrading SQL Server 2008 R2 to SQL Server 2012. We have a separate SSRS 2008 instance running on a separate machine which interact with these existing 2008 database servers and create various reports.

My architect's recommended that we just create a new VM and get SQL Server 2012 with existing Data. (You install a new environment, copy your metadata and data to the new environment, and configure the new environment to use existing content.)

He suggested that we need not to make any changes to SSRS instance which is running on older version. He suggested just to re-use existing SSRS instance and point to new SQL Server 2012.

My question is about the feasibility of this solution. Will that work? Can you have SSRS instance running on older version but interacting with migrated SQL Db Server 2012 instance. Any challenges/possible issues?

Your SSRS deployment just needs access to two databases ReportServer and ReportServerTempDB. It doesn't matter where they are hosted as long as they are accessible.

When you move these databases to the new server you will need to open Reporting Services Configuration Manager and redirect SSRS to the new SQL 2012 server. You can change the connection on the database tab.

If the connection test goes through OK then you should be ready to go.

One thing to make sure of is that all the logins from the old server are present on the new server before transferring the databases. If they are not the permissions for any missing users will be dropped.