Table Locking When using Unions When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed?

For example, 100 individual select statements are concatenated together via UNION/UNION ALL.  (Assuming they are resolved sequentially) If the first SELECT in the transaction is completed and another transaction in a different session attempts to access the same data, is the data still locked by the UNION?

I am just trying to get an understanding on how UNIONs behave with the physical tables while they are still executing.

For the query which prompted this there are 7 different statements in the UNION (Not UNION ALL).  With this particular question I was just wanting to get an understanding on the back-end architecture of how the UNION/UNION ALL worked.  The query which prompted this question causes a lot of blocking but only runs for about a minute, so I was trying to get an understanding on if it was the whole query causing it or just a few of the individual pieces.

I have added a follow-up question here: Backend Table/Row Locking Structure using CTE Queries

All the select .. union .. select .. bits count as a single statement. I can't find an article in the documentation to justify this but I can offer supporting evidence.

First, the semicolon is the Transact-SQL statement terminator. There is only one place in your query it will be accepted and that's at the very end of the very last SELECT. Second, the execution plan consists of a single tree, indicative of a single statement. In contrast two queries submitted as a single batch would cause two disjoint execution trees.

If there is an explicit transaction in place the select..union becomes part of that and the locks are held until the COMMIT or ROLLBACK. If you have an implicit transaction, by which I mean autocommit, not SET IMPLICIT_TRANSACTIONS, it spans all of the selects. Locks will be held for however long they would be held absent the UNION, governed by isolation level, query options (TABLOCKX etc.), lock escalation, trace flags .. and all the other things that affect this.


t-sql to create tables using a declared variable I am attempting to create multiple tables in multiple databases using declared variables for the db_name, table_name , column_name and column_type. Is this feasible ? I have to create the same tables for 14 databases. The tables are the same for the databases but there are multiple tables and multiple columns.

Sample:

DECLARE @DBName VARCHAR(250) = (SELECT [name] FROM sys.databases WHERE database_id >= 337); --Newly Created Databases
DECLARE @TableName VARCHAR(250) = (SELECT TableName FROM DB.Table.Tables WHERE Enabled = 1);
DECLARE @TableId INT = (SELECT Id FROM DB.Table.Tables WHERE Enabled = 1);
DECLARE @ColumnName VARCHAR(250) = (SELECT ColumnName FROM DB.Table.Columns WHERE Enabled = 1 AND TableId = @TableId);
DECLARE @ColumnType VARCHAR(250) = (SELECT ColumnType FROM DB.Table.Columns WHERE Enabled = 1 AND TableId = @TableId);

USE [master];

CREATE TABLE [@DBName + '.dbo.' + @TableName] (@ColumnName + @ColumnType);

You'll need to employ a little semi-advanced dynamic T-SQL for this task.

First, I'll create a small MCVE in tempdb.  In future, please create this part yourself when asking questions here :-)

SET NOCOUNT ON;
USE tempdb;
IF SCHEMA_ID(N'Table') IS NOT NULL
BEGIN
    DROP TABLE [Table].Columns;
    DROP TABLE [Table].Tables;
    DROP SCHEMA [Table];
END
GO
CREATE SCHEMA [Table]
CREATE TABLE [Tables]
(
    TableID int NOT NULL
        CONSTRAINT Tables_pk
        PRIMARY KEY CLUSTERED
    , TableName sysname NOT NULL
    , [Enabled] bit NOT NULL
)
CREATE TABLE [Columns]
(
    ColumnID int NOT NULL
        CONSTRAINT Columns_pk
        PRIMARY KEY CLUSTERED
    , TableID int NOT NULL
        CONSTRAINT Columns_TableID
        FOREIGN KEY
        REFERENCES [Tables](TableID)
    , ColumnName sysname NOT NULL
    , ColumnType nvarchar(150) NOT NULL
    , Nullable bit NOT NULL
    , [Enabled] bit NOT NULL
);
GO

Here, I'm create a single row in [Table].Tables, with two columns a, and b.

INSERT INTO [Table].Tables (TableID, TableName, [Enabled])
VALUES (1, N'a', 1);

INSERT INTO [Table].Columns (ColumnID, TableID, ColumnName, ColumnType, Nullable, [Enabled])
VALUES (1, 1, N'b', N'nvarchar(30)', 1, 1)
    , (2, 1, N'c', N'int', 0, 1)
GO

This is the "interesting" dynamic T-SQL code.  It cross joins to sys.databases so that a CREATE TABLE statement is generated for every database on the instance.  I'm using WHERE database_id > 5 because I don't have an instance with more than 337 databases - so you'll need to modify that to suit your needs.

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT N'USE ' + QUOTENAME(d.name) + N';CREATE TABLE '
    + QUOTENAME(t.TableName)
    + N'( ' + STUFF((SELECT N', '
        + c.ColumnName
        + N' ' + c.ColumnType
        + N' '
        + CASE WHEN c.Nullable = 1 THEN N'NULL'
            ELSE N'NOT NULL'
          END
    FROM [Table].Columns c
    WHERE t.TableID = c.TableID
        AND c.[Enabled] = 1 FOR XML PATH (N'')), 1, 2, N'') + N');'
FROM [Table].Tables t
    CROSS JOIN sys.databases d
WHERE t.[Enabled] = 1
    AND d.database_id > 5;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The code will "print" a CREATE TABLE statement for each database, which you could simply copy-and-paste into a new query window to actually execute.  You could replace the PRINT @cmd; with EXEC sys.sp_executesql @cmd; to automatically run each CREATE TABLE statement, however I wouldn't suggest doing that until you're certain this works the way you expect.

Notice, the code above includes the Nullable column - that wasn't in your example code.  It's important to declare the nullability for every column when creating a table.

Also, please don't use reserved words for table names; i.e. don't call a table "Table".  It's frustratingly hard to type and hard to read later.


Run Transaction Log backups only during Work hours? Currently we have a number of databases that are in full recovery model. This is a conscious decision.

We also run the transaction log backups every 15 minutes.

But we run the transaction log backups only during the hour when people work with the databases, that is between 7 in the morning until 8 in the evening.

My question is:

On a technical level, is this a good practice or not?

On a technical level: is this a good practice or not?

I would say not. If the database is experiencing no activity at all during the business off hours, then taking the backups is very low overhead. On the other hand, if the database is experiencing any activity during business off hours then not taking the backups is a serious problem.

My stance is that taking the backup has advantages:

is "future proof": if someone adds an off-hours job, your backups will 'just-work'
less complex, is easier to explain/understand/operate: no special hours for log backups. No special recovery procedure for 'off hours' vs. 'on hours'
it does no damage


Replicating redis foreign table via postgresql synchronous replication I have synchronous replication between db server, DB1 and DB2. Tested, working.
I have a redis server (RD1), integrated with DB1 (master) via redis_fdw, no issues here. I was expecting foreign redis table to by synched between DB1 and DB2 after sync replication but it does not. I can see the table on DB2 (slave) when I type "\d" in slave psql but when I want to select records, I get: "ERROR:  could not access file "$libdir/redis_fdw": No such file or directory". The question is, how can I get redis updates from postgresql slave via postgresql sync. It is possible? I can integrate the slave DB with redis server but that would break the DB sync.
Thanks.

a Foreign Data Wrapper (fdw) point and interpret the data (keep no data),its like a view. Is the redis_fdw compiled on the slave side? if not complile it and master and salve points to your RD1-Table.


Loop through all users in MySQL and add REQUIRE SSL flag How do I loop through all users (we have > 3000) in a MySQL database, and add the REQUIRE SSL flag to the every account? I don't want to modify the existing users permissions, host, or password, simply add REQUIRE SSL

You could GRANT each existing user the special no-privilege USAGE privilege, which doesn't change their existing privileges but can be used to REQUIRE SSL for an existing user.

USAGE can be specified to create a user that has no privileges, or to specify the REQUIRE or WITH clauses for an account without changing its existing privileges.
— http://dev.mysql.com/doc/refman/5.6/en/grant.html

mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------+
| Grants for sqlbot@%                                                                         |
+---------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sqlbot'@'%' IDENTIFIED BY PASSWORD '*XXX' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'sqlbot'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------+
| Grants for sqlbot@%                                                                                |
+---------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sqlbot'@'%' IDENTIFIED BY PASSWORD '*XXX' REQUIRE SSL WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The above approach adds the SSL restriction without otherwise changing my privileges.
Alternately, you should be able to manipulate the mysql.user table directly with queries.  The only caveats here: don't add this to your own SUPER account until you know you've got SSL working correctly, and remember to FLUSH PRIVILEGES; after manually modifying the grant tables.  Without that last step, the changes won't be noticed by the server until the next restart, since the grant tables are cached and only changes made by GRANT or REVOKE are noticed by the server otherwise.
This query should force all users to use SSL.  You may want to modify it to suit your requirements, based on what a correctly-configured user looks like in your user table, or try it on only one user and not the entire user base, to confirm that the behavior you get is the behavior you expect.
mysql> UPDATE mysql.user SET ssl_type = 'any' WHERE ssl_type = '';
mysql> FLUSH PRIVILEGES;

There is no reason I can think of that this should be problematic, but please use this suggestion with caution in production.


Best way to allow update exceptions on a cell level I've got 2 tables, a base table - tBase - and a holding table used to update that base table - tUpdate.  (Note: actual tables will have around 25 columns)

Table tBase

ID  |  name  |  type  |  color  |  size
----------------------------------------
 1      A        Z        red       big
 2      B        Y        blue      tiny
 3      C        X        red       tiny
 4      D       NULL      NULL      NULL

Table tUpdate

ID  |  name  |  type  |  color  |  size
----------------------------------------
 1      A        Z        red       big
 2      B        Z        blue      tiny
 3      C        X        NULL      tiny
 4      D        W        gold      big
 5      E        Y        grey      cat

I would like to design a way to say specific cells should not be updated, but the best I can come up with at the moment involves multiple updates.  Basically, my idea involves having a third table - tExclude that would look like this:

ID  |  ColName
---------------
 2      type
 4      size
 4      color

The actual UPDATE statements would then run for each column and exclude any IDs that are in the tExclude table.  I'd rather not have to make 25 different updates that use the same 2 tables, and I feel like there's got to be a better way of doing it that I'm not finding.

I've looked into CROSS APPLY a bit but couldn't figure out how it could help.  I also looked into MERGE but that also didn't seem to have a way to do what I'm looking for.

This sounds like the classic use-case for a SQL Server-based cursor.  Yes, I just said the "c" word.

Take this code, which uses your sample data:

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.tExclude', N'U') IS NOT NULL
DROP TABLE dbo.tExclude;
CREATE TABLE dbo.tExclude
(
    ID int NOT NULL
    , ColName sysname NOT NULL
);

IF OBJECT_ID(N'dbo.tBase', N'U') IS NOT NULL
DROP TABLE dbo.tBase;
CREATE TABLE dbo.tBase
(
    ID int NOT NULL
    , item_name varchar(30)
    , item_type varchar(30)
    , color varchar(30)
    , size varchar(30)
);

IF OBJECT_ID(N'dbo.tUpdate', N'U') IS NOT NULL
DROP TABLE dbo.tUpdate;
CREATE TABLE dbo.tUpdate
(
    ID int NOT NULL
    , item_name varchar(30)
    , item_type varchar(30)
    , color varchar(30)
    , size varchar(30)
);

Sample data:

INSERT INTO dbo.tExclude (ID, ColName)
VALUES (1, 'item_name')
    , (1, 'size');

INSERT INTO dbo.tBase (ID, item_name, item_type, color, size)
VALUES (1, 'A', 'Z', 'red', 'big')
    , (2, 'B', 'Y', 'blue', 'tiny')
    , (3, 'C', 'X', 'red', 'tiny')
    , (4, 'D', NULL, NULL, NULL);

INSERT INTO dbo.tUpdate(ID, item_name, item_type, color, size)
VALUES (1, 'A', 'Z', 'red', 'big')
    , (2, 'B', 'Y', 'blue', 'tiny')
    , (3, 'C', 'X', 'red', 'tiny')
    , (4, 'D', 'W', 'gold', 'big')
    , (5, 'E', 'Y', 'grey', 'cat');

At this point, the dbo.tBase table looks like:

╔════╦═══════════╦═══════════╦═══════╦══════╗
║ ID ║ item_name ║ item_type ║ color ║ size ║
╠════╬═══════════╬═══════════╬═══════╬══════╣
║  1 ║ A         ║ Z         ║ red   ║ big  ║
║  2 ║ B         ║ Y         ║ blue  ║ tiny ║
║  3 ║ C         ║ X         ║ red   ║ tiny ║
║  4 ║ D         ║ NULL      ║ NULL  ║ NULL ║
╚════╩═══════════╩═══════════╩═══════╩══════╝

Code to do the desired updates and inserts:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
    cmd nvarchar(max) NOT NULL
);

DECLARE @cmd nvarchar(max);
DECLARE @ID int;
DECLARE @Type varchar(6);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT tu.ID
    , CASE WHEN tb.ID IS NULL THEN 'INSERT' ELSE 'UPDATE' END
FROM dbo.tUpdate tu
    LEFT JOIN dbo.tBase tb ON tu.ID = tb.ID;
OPEN cur;
FETCH NEXT FROM cur INTO @ID, @Type;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Type = 'UPDATE'
    BEGIN
        SET @cmd = N'
WITH src AS
(
    SELECT c = QUOTENAME(c.name) + '' = '' + ''tu.'' + QUOTENAME(c.name)
    FROM sys.columns c
        INNER JOIN sys.tables t ON c.object_id = t.object_id
    WHERE t.name = ''tBase''
        AND c.name <> ''ID''
    EXCEPT
    SELECT QUOTENAME(e.ColName) + '' = '' + ''tu.'' + QUOTENAME(e.ColName)
    FROM dbo.tExclude e
    WHERE ID = ' + CONVERT(nvarchar(max), @ID) + N'
)
SELECT ''UPDATE dbo.tBase
SET '' + (STUFF((SELECT '', '' + src.c FROM src FOR XML PATH ('''')), 1, 2, '''')) + ''
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = ' + CONVERT(nvarchar(max), @ID) + N';''';
        INSERT INTO #t
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @cmd = N'INSERT INTO dbo.tBase (ID, item_name, item_type, color, size)
SELECT ID, item_name, item_type, color, size
FROM dbo.tUpdate tu
WHERE tu.ID = ' + CONVERT(nvarchar(max), @ID) + N';';
        INSERT INTO #t (cmd)
        SELECT @cmd;
    END
    FETCH NEXT FROM cur INTO @ID, @Type;
END
CLOSE cur;
DEALLOCATE cur;

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT t.cmd
FROM #t t;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    EXEC sys.sp_executesql @cmd;
    PRINT N'';
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The code above uses a couple of cursors to create and execute INSERT or UPDATE statements as required.  The UPDATE statements never update columns for rows included in the dbo.tExclude table.

The generated INSERT/UPDATE statements look like:

UPDATE dbo.tBase
SET [color] = tu.[color], [item_type] = tu.[item_type]
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = 1;

UPDATE dbo.tBase
SET [color] = tu.[color], [item_name] = tu.[item_name], [item_type] = tu.[item_type], [size] = tu.[size]
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = 2;

UPDATE dbo.tBase
SET [color] = tu.[color], [item_name] = tu.[item_name], [item_type] = tu.[item_type], [size] = tu.[size]
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = 3;

UPDATE dbo.tBase
SET [color] = tu.[color], [item_name] = tu.[item_name], [item_type] = tu.[item_type], [size] = tu.[size]
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = 4;

INSERT INTO dbo.tBase (ID, item_name, item_type, color, size)
SELECT ID, item_name, item_type, color, size
FROM dbo.tUpdate tu
WHERE tu.ID = 5;

And the dbo.tBase table now looks like:

SELECT *
FROM dbo.tBase;

╔════╦═══════════╦═══════════╦═══════╦══════╗
║ ID ║ item_name ║ item_type ║ color ║ size ║
╠════╬═══════════╬═══════════╬═══════╬══════╣
║  1 ║ A         ║ Z         ║ red   ║ big  ║
║  2 ║ B         ║ Y         ║ blue  ║ tiny ║
║  3 ║ C         ║ X         ║ red   ║ tiny ║
║  4 ║ D         ║ W         ║ gold  ║ big  ║
║  5 ║ E         ║ Y         ║ grey  ║ cat  ║
╚════╩═══════════╩═══════════╩═══════╩══════╝


Join 3 tables returns too many results I have 3 tables: users, posts, videos. The last 2 tables have user_id that associate with users.id (first table).

The problem is that when I try to run inner join query and return all posts and videos that have user_id and exists in both tables, I get multiple same records.

select * from `users`
inner join `videos` on `users`.`id` = `videos`.`user_id`
inner join `posts` on `users`.`id` = `posts`.`user_id`
where `users`.`id` = ?
limit 50 offset 0

If I run the queries separately, I get 6 rows and 8 rows but with the join I get 48 rows.

If you want 14 rows in the result (6+8), you need to use UNION of the two queries you already have.  Just take care for both to have the same number of columns (and same datatypes in the respective columns.)

It will be something like:

SELECT u.*,                              -- list of wanted columns from the users table
       'Video'     AS type
       v.id        AS item_id,           -- and the videos table
       v.posted_at AS posted_datetime
FROM users AS u
  INNER JOIN videos AS v
    ON u.id = v.user_id
WHERE u.id = ?

UNION ALL

SELECT u.*,
       'Post'      AS type
       p.id        AS item_id,           -- and the posts table
       p.posted_at AS posted_datetime
FROM users AS u
  INNER JOIN posts AS p
    ON u.id = p.user_id
WHERE u.id = ?

ORDER BY posted_datetime                 -- ORDER BY some_column
LIMIT 50 OFFSET 0 ;


MySQL - Move data between partitions aka re-partition I have a mysql table whose partitions look as below

p2015h1 - Contains data where date < 2015-07-01 (Has data from 2016-06-01. Hence only month worth of data)
p2015h2 - Contains data where date < 2016-01-01
p2016h1 - Contains data where date < 2016-07-01
p2016h2 - Contains data where date < 2017-01-01

I'd like the new partitions to be quarterly based as below -

p0 - Contains data where date < 2015-10-01
p1 - Contains data where date < 2016-01-01
p2 - Contains data where date < 2016-04-01
p3 - Contains data where date < 2016-07-01

I started by reorganizing the first partition & executed the below command. All went well.

alter table `table1` reorganize partition `p2015half1` into (partition `p0` values less than ('2015-10-01'));

Now as the existing partition p2015h2 has data that includes data upto 2015-10-01, how could I move this part into the partition p0 ? I would need to do the same thing with the other partitions too as I continue building the new ones.

I did try to remove partitioning on the table fully, but, the table is billions of rows in size & hence the operation will take days. Post this I will have to rebuild the partitions which will take days again. Hence, I decided to take the approach of splitting partitions.

I'm stuck at this point in time. I'd fully appreciate any guidance here please.

You need to split each partition into two partitions?  Something like this (please verify the syntax):

ALTER TABLE table1  REORGANIZE PARTITION p2015half1 INTO
    partition `p0` values less than ('2015-10-01'),
    partition `p0` values less than ('2016-01-01') ;

That is, take any number of partitions (1 in this case), copy the data into any number of new partitions (2 in this case).

But I need to ask "why"?  Would you show us SHOW CREATE TABLE and explain why you chose to use PARTITIONing.  I ask this question because everyone seems to have the impression that partitioning is beneficial.  Often it provides no benefit, especially not in performance.

The next question has to do with "billions of rows".  To do anything with that much data takes a long time -- simply because of I/O speed.  What kinds of queries are you doing on the table?  Do you have summary tables?  Do you purge "old" data?


Does a Postgres VACUUM free up space for any database object? If I do a VACUUM on a Postgres table (without the FULL option) I know that it should make any free-space available to the database, but not the operating system.

Should it make the freespace available to any database object? or just the table that's been vacuumed?

After a VACUUM the table will be as big as before, just more of the space will be free and available for future INSERTs and UPDATEs. In other words, the file won't shrink (1). So the space will not be available for other tables, only for the table that has been vacuumed.

Note (1): Sometimes the file will shrink. If the last block is completely empty after the VACUUM, PostgreSQL will try to truncate all empty blocks off the end of the table.


Is my theory on INSERT deadlocks correct? I originally had this (as three separate PG queries, from my applications):
INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
INSERT INTO table3 (col1, col2, col3) VALUES
(
    (SELECT id FROM table1 WHERE blabla = $1),
    (SELECT id FROM table2 WHERE blabla = $2),
$3);

After finally learning about transactions (or at least I thought that I had finally learned about them...), I wanted to group three INSERTs into one transaction because they are deeply related. The third INSERT expects those other tables to be populated before it runs, and the two first INSERTs are meaningless without the third, "main" one. So I naively did this:
BEGIN;
    INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table3 (col1, col2, col3) VALUES
    (
        (SELECT id FROM table1 WHERE blabla = $1),
        (SELECT id FROM table2 WHERE blabla = $2),
    $3);
COMMIT;

Sadly, it now started logging a bunch of this when running the third INSERT:
pg_query_params(): Query failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block
pg_query_params(): Query failed: ERROR:  deadlock detected
DETAIL:  Process 6276 waits for RowExclusiveLock on relation 939516 of database 53142; blocked by process 7508.
Process 7508 waits for AccessExclusiveLock on relation 939490 of database 53142; blocked by process 6276.

This confused me fundamentally, but then I thought that maybe, the transaction block causes the third INSERT to not "see" the previous INSERTs because they have not yet been committed? Is that it?
I have now changed it into:
BEGIN;
    INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
COMMIT;
INSERT INTO table3 (col1, col2, col3) VALUES
(
    (SELECT id FROM table1 WHERE blabla = $1),
    (SELECT id FROM table2 WHERE blabla = $2),
$3);

So far, it has not logged any deadlocks. It appears as if my theory is correct, but I wish to hear if this is a false conclusion by me.
Also, if this is the case, I guess there is no way to "group" all three INSERTs? Since I have to commit after the first two for the third to be possible?
Also, I'm confused as to why, in the second code example, the third INSERT's internal SELECTs don't just return "null" values since that's what normally happens when the values don't exist in the tables. I assume it "knows" that it's inside a transaction with relevant changes having been made? That's got to be it?

Process 7508 waits for AccessExclusiveLock on relation 939490

Nothing in your shown code would take an AccessExclusiveLock on a relation.  The 7508 member of your deadlocked pair is doing something different.  Look in the log file to see if there is a clue about what that is. (The log file will show what statement it was running when the deadlock occured, but that might not be the same statement as what took out the lock, but at least it will be more information than you currently have)

maybe, the transaction block causes the third INSERT to not "see" the previous INSERTs because they have not yet been committed? Is that it?

No, a statement in a transaction can see INSERTs done by earlier statements in the same transaction.


Getting error on SQL Server I am getting these error messages one after the other every week on Win 2008 and SQL 2008 R2. MAX DOP is set at 1.  Any idea how I can solve this?

Thanks.

  DESCRIPTION:   A fatal error occurred while reading the input stream
  from the network. The session will be terminated (input error: 64,
  output error: 0).  DESCRIPTION:   The client was unable to reuse a
  session with SPID 60, which had been reset for connection pooling. The
  failure ID is 29. This error may have been caused by an earlier
  operation failing. Check the error logs for failed operations
  immediately before this error message.

Unless you are running the newest builds this should be fixed by applying the latest CU (look down in the comments).  Based on this bug/fix it appears to be a problem the CLR garbage collector.


I am trying to represent the value of gender field with 'male' if 1 and 'female' is 0 Need to convert gender field has data type as bit.

I used this syntax:

Select case when CAST([GenderIsMale] AS bit)=1 then 'Male'
when CAST([GenderIsMale] AS bit)=0 then 'Female'
else  CAST([GenderIsMale] AS bit)
end

Returns the error:

  Conversion failed when converting the varchar value 'Male' to data type bit.

The problem is that you have two different return types, depending on the value of [GenderIsMale]; the two WHEN conditions return a string while the ELSE returns a BIT. It is easier to see if you format the code nicely:

Select case
    when CAST([GenderIsMale] AS bit)=1 then 'Male'   -- return type = VARCHAR
    when CAST([GenderIsMale] AS bit)=0 then 'Female' -- return type = VARCHAR
    else CAST([GenderIsMale] AS bit)                 -- return type = BIT
end

You can test this as follows:

DECLARE @GenderIsMale INT = 1;

Select case
    when CAST(@GenderIsMale AS bit)=1 then 'Male'
    when CAST(@GenderIsMale AS bit)=0 then 'Female'
    else CAST(@GenderIsMale AS bit)
end

Returns:

  Msg 245, Level 16, State 1, Line 28
  Conversion failed when converting the varchar value 'Male' to data type bit.

You can fix this by returning a string (or literal NULL) in the ELSE condition (pick one of those ELSE conditions, depending on what you are trying to accomplish):

SELECT CASE CAST([GenderIsMale] AS BIT)
    WHEN 1 THEN 'Male'
    WHEN 0 THEN 'Female'
    ELSE 'other string'
    --ELSE CONVERT(VARCHAR(50), [GenderIsMale]) -- if column is a numeric type
    --ELSE [GenderIsMale] -- if column is a string type
    --ELSE NULL
END;

Please note:

Depending on the datatype of the GenderIsMale column and the data in it, it is possible that there is an additional source of the error: the CAST of the value into BIT. For example, if the datatype is VARCHAR, then values of empty string, 'true', 'false', and strings of just digits 0 - 9 (no spaces, commas, periods, or minus sign) will convert, but anything else will error. Since you are using SQL Server 2012, you can switch to using TRY_CAST (or even TRY_CONVERT) to get around that.
The commented-out ELSE conditions are mainly to show the possible variations for getting the resulting expression to be consistent across all branches of the CASE statement. But in the scenario here of having a BIT / boolean value, if the datatype of the GenderIsMale column is a number, then the only value that could fall through to the ELSE is a NULL, hence you would use ELSE NULL. But if the datatype is a string, then you would use ELSE [GenderIsMale].


Is it possible to validate DML statements? Suppose that I want to delete a user named 'abc cba' from the database. So I run the following query:

DELETE FROM table WHERE fname = 'abc' AND last name = 'cba'; commit;

Now, there can be more users with this name I do not know about. The obvious choice would be to run select statement beforehand and check if I am deleting what I really wanted to delete, but I am still human and I can forget that and end up searching for backups to retrieve lost data.

My question is: is there a way to impose certain rules on statements after which they will not execute the DML statement (for example, do not execute the delete if the number of rows deleted exceeds certain threshold)?

I can only speak for SQL Server, but the only way this could be done is through a DML trigger (as far as I know).  Take the below for example:

use TestDB;
go

create table dbo.TestTriggerTable
(
    id int identity(1, 1) not null,
    some_int int not null
        default 1
);
go

insert into dbo.TestTriggerTable
default values;
go 1000

create trigger DeleteMaxRows
on dbo.TestTriggerTable
after delete
as

    if (select count(*) from deleted) > 10
    begin
        raiserror('Cannot delete more than 10 rows', 16, 1);
        rollback;
    end
go

select *
from dbo.TestTriggerTable;

-- this fails and rolls back
delete from dbo.TestTriggerTable
where id < 20;
go

-- this is successful
delete from dbo.TestTriggerTable
where id < 8;
go

There are a few problems with this approach though, and I don't believe this to be a good design.  First off, you have hard-coded requirements within the trigger (whether you're doing a set row count, or a percentage of rows...it's still hard-coded).  An application would have to be designed accordingly to ensure retry with different logic.

What this really comes down to is having a correctly designed database, and correctly constructed DML statements to ensure that the data that is being modified is the data you want, instead of having to check a row count of the deleted rows.

In my opinion, you should be spending design time in figuring out how to redesign so you won't need this check.


wm_concat in a specific order I have a query that makes something like this:

+---------+---------+------+
| Patient | Pattern | Rank |
+---------+---------+------+
| a       |      0  |    1 |
| a       |      1  |    2 |
| a       |      0  |    3 |
| a       |      0  |    4 |
| b       |       1 |    1 |
| b       |       1 |    2 |
| c       |       0 |    1 |
+---------+---------+------+

I would like to turn it into something like this:

+---------+----------+
| Patient |  Patternstring |
+---------+----------+
| a       |     0100 |
| b       |       11 |
| c       |        0 |
+---------+----------+

However, I'm getting stuff like this:

+---------+----------+
| Patient |  Patternstring |
+---------+----------+
| a       |     1000 |
| b       |       11 |
| c       |        0 |
+---------+----------+

The original query is ordered by patient, then rank. The way I'm producing things like the current output table is with this:

SELECT PATIENT_ID, WM_CONCAT(PATTERN) as PATTERNSTRING

FROM
(That query)

GROUP BY PATIENT ID;

I'm guessing this has something to do with the way WM_CONCAT functions. Even though the query that WM_CONCAT is reading would have it concatenate everything in the right order (assuming it was reading top-bottom), it seems to be reading things in more-or-less a random order. Is there any trick I can use to get WM_CONCAT to work properly?

I know that LISTAGG would let me solve this easily, but I am using Oracle 11.1 and upgrading isn't an option.

The research I did made it seem like I need to either write my own aggregation or upgrade Oracle. I ended up doing neither, and exported the query through Tableau, formatting it in a sheet appropriately, then exporting that sheet to excel and making an aggregation column.


Query To Show Monthly Income I am attempting to use Cross Apply to get a SUM() for each month and create a rolling trend report.  My issue is that with my query I keep getting an error of

  Msg 207, Level 16, State 1, Line 6
  Invalid column name 'TotalCost'.

Which is obviously an error in my syntax, but I just can not seem to find it.  Here is the query that I attempted (and have been toying with for 3 hours now)

SELECT
a.[iDate]
,TotalCost=SUM(ISNULL([value1],0)+ISNULL([value2],0)+ISNULL([value3],0)+ISNULL([value4],0)+ISNULL([value5],0))
,Rolling12Months=CASE
                    WHEN ROW_NUMBER() OVER (ORDER BY a.[iDate]) < 12 THEN NULL
                    ELSE TotalCost+Value
             END
FROM numbergame a
CROSS APPLY
(
  SELECT
  VALUE
  FROM
  (
      SELECT
      b.[iDate]
        ,VALUE=SUM(ISNULL([value1],0)+ISNULL([value2],0)+ISNULL([value3],0)+ISNULL([value4],0)+ISNULL([value5],0))
    FROM numbergame b
    WHERE b.[iDate] < a.[iDate]
    GROUP BY [iDate]
  ) b
) b
GROUP BY [iDate]
ORDER BY a.[iDate]

and in all transparency it is Post # 4 taken from Simple Talk.  Now here is my sample DDL in a mm/dd/yyyy format with only one day for each month, but it should get the point across.

Create Table numbergame
(
  iDate date
  ,value1 float
  ,value2 float
  ,value3 float
  ,value4 float
  ,value5 float
)

INSERT INTO numbergame VALUES
('01/01/2015', 10, 20, 30, 40, 50)
,('02/01/2015', 1, 2, 3, 4, 5)
,('03/01/2015', 5, 4, 3, 2, 1)
,('04/01/2015', 9, 8, 7, 6, 5)
,('05/01/2015', 5, 6, 7, 8, 9)
,('06/01/2015', 10, 8, 6, 4, 2)
,('07/01/2015', 11, 12, 13, 14, 15)
,('08/01/2015', 15, 14, 12, 13, 11)
,('09/01/2015', 3, 3, 3, 3, 3)
,('10/01/2015', 4, 4, 4, 4, 4)
,('11/01/2015', 2, 2, 2, 2, 2)
,('12/01/2015', 4, 4, 4, 4, 4)

How should my query (or if there is a better/quicker way to do this) be altered so that I can display my rolling trend?


Index with ops for `LIKE` and `=` queries In my Rails 5 with PostgreSQL 9.5 app I have 2 frequent queries:

SELECT * FROM table WHERE lower(varchar_col) = 'some_string' AND int1_col = some_int AND int2_col = some_other_int

-- and

SELECT * FROM table WHERE lower(varchar_col) LIKE 'some_str%' AND int1_col = some_int

table is about 1.5m rows and quickly growing.

So, I've created 2 indices for both of these queries:

CREATE INDEX index_1 ON table USING btree (lower((varchar_col)::text), int1_col, int2_col)

-- and

CREATE INDEX index_2 ON table USING btree (lower((varchar_col)::text) varchar_pattern_ops, int1_col)

I have 2 questions, maybe they are a little noob, I'm not good in DB performance:

1.Can I use only one index for both of these queries? Something like:

CREATE INDEX index ON table USING btree (lower((varchar_col)::text) varchar_pattern_ops, int1_col, int2_col)

Does varchar_pattern_ops shows the same performance for LIKE and = queries? Will PostgreSQL planner use multicolumn index for 3 columns in query for 2 columns?

2.I noticed that Rails generate index with expression lower((varchar_col)::text, but my varchar_col has varchar type, not text. Does this matter and Rails made some mistake and I need to write CREATE INDEX query to specifically include column type in query?

Thanks for any help!

P.S. Of course, table's, columns' and indices' name are changed for better reading.

Using an index which is somewhere between the one suggested by @ypercubeᵀᴹ and your index_2 ... which would be:

CREATE INDEX index_1 ON t
    USING btree
          (int1_col,
          lower((varchar_col)::text) varchar_pattern_ops,
          int2_col) ;

... seems to be a good compromise.

You put first the two columns that you use always, and add the third one as the last. You add the varchar_pattern_ops so that queries with LIKE use the index.

From the two always used columns, put the one which is always tested for equality first, the one which is tested with other operators (>=, <=, like) second. If both were tested for equality, you normally would want to have the one which is more discriminant [which means, more or less, that has a bigger number of different values] first. If several columns are equally discriminant, you put first the ones with the datatypes easier/faster to compare (for instance: fixed-size values such as integers would go before varying length values such as texts).

These are the exeuction plans of the two queries you want to perform:

 EXPLAIN ANALYZE
 SELECT
     *
 FROM
     t
 WHERE
     lower(varchar_col) = 'some_string'
     AND int1_col = 5678
     AND int2_col = 1234 ;

 | QUERY PLAN                                                                                |
 | :------------------------------------------------------------------------------------------------------------- |
 | Index Scan using index_1 on t  (cost=0.42..8.44 rows=1 width=40) (actual time=0.007..0.008 rows=1 loops=1)     |
 |   Index Cond: ((int1_col = 5678) AND (lower((varchar_col)::text) = 'some_string'::text) AND (int2_col = 1234)) |
 | Planning time: 0.274 ms                                                                                |
 | Execution time: 0.023 ms                                                                                |

 EXPLAIN ANALYZE
 SELECT * FROM t WHERE lower(varchar_col) LIKE 'some_str%' AND int1_col = 5678 ;

 | QUERY PLAN                                                                                |
 | :------------------------------------------------------------------------------------------------------------------------------------------- |
 | Bitmap Heap Scan on t  (cost=4.44..12.16 rows=1 width=40) (actual time=0.013..0.015 rows=3 loops=1)                                          |
 |   Recheck Cond: (int1_col = 5678)                                                                                |
 |   Filter: (lower((varchar_col)::text) ~~ 'some_str%'::text)                                                                                |
 |   Heap Blocks: exact=1                                                                                |
 |   ->  Bitmap Index Scan on index_1  (cost=0.00..4.44 rows=2 width=0) (actual time=0.005..0.005 rows=3 loops=1)                               |
 |         Index Cond: ((int1_col = 5678) AND (lower((varchar_col)::text) ~>=~ 'some'::text) AND (lower((varchar_col)::text) ~<~ 'somf'::text)) |
 | Planning time: 0.177 ms                                                                                |
 | Execution time: 0.027 ms                                                                                |

Both query plans use your index and are fast (although, my simulated data might not be realistic at all).

dbfiddle here


send_db_mail with attachment query can't see user databases I have a SQL Agent job that calls send_db_mail with the following code:

DECLARE @FileName VARCHAR(30)
SET @FileName= 'FileName'+CONVERT(VARCHAR(20),GETDATE(),112)+'.csv'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Admins',
    @recipients = 'my@email.address.com',
    @body = 'Attached are the results of the query.',
    @query = 'SELECT columns from MyDatabase.dbo.MyTable',
    @subject = 'Test Run',
    @attach_query_result_as_file = 1 ,
    @query_attachment_filename = @FileName,
    @query_result_separator = ',',
    @query_result_no_padding=1,
    @query_result_header = 0,
    @query_result_width = 32767,
    @exclude_query_output=1,
    @append_query_error = 1;

When I run that code through SSMS, I get an email, and it works fine. When I run the job, it succeeds, but I receive no email.

I have checked the following:

Nothing appears in sysmail_allitems
No errors appear in sysmail_event_log
If I change @query = 'select 1 as squirrel' it will send an email from the job.
If I change @query = 'select name from master.sys.databases' it will send an email from the job.

There is no error message given when no email is sent, and the job even lists as succeeded.

I'm not sure where to look next - can anyone suggest where to look for problems?

When you launch the query through SSMS, it will use your account, but when it is launched through SQLAgent job, it will use the account that runs the service. Can can double check that in send_request_user column of sysmail_allitems table.

Maybe they have different rights ?


Using pgpool and PostgreSQL in differents hosts. I want to use pgpool but I did some research and I have a doubt, if neccesary install PostgreSQL and pgpool in the same machine? When I did my first configuration and installation in a same host. But this gave me some problems, some time Postgresql services can't start and I want to separate them? It is possible?

They do not have to be on the same host; otherwise lots of the useful things you can do with PgPool-II would not be possible.

PgPool-II communicates with PostgreSQL over PostgreSQL's TCP/IP protocol.

The reason it wouldn't start on the same host is probably because you were trying to run both PgPool-II and PostgreSQL on the same port (5432).


How to solve the error "Error 'Got error 0 'No error' from NDBCLUSTER' on query"? I'm currently working with a large network of MySQL 5.1 DBs (using MyISAM tables) and I'm evaluating a move to MySQL Cluster. I would like to have the MySQL Cluster replicate off of one of the existing 5.1 hosts to ensure that it can handle the load we want to send it, and to test failure conditions with real data loads, etc.

So far, I've got the MySQL Cluster mysqld loaded up with all the MyISAM tables and I've got it connected to the replication master. Replication is working just fine (5.1 MyISAM -> 5.5 MyISAM). However, as soon as I convert one of the tables to ENGINE=NDBCLUSTER I get these errors:

121027 16:09:40 [ERROR] Slave SQL: Error 'Got error 0 'No error' from NDBCLUSTER' on query. Default database: 'accounts'. Query: 'UPDATE heartbeat SET ts = NOW() WHERE id = 1', Error_code: 1296
121027 16:09:40 [Warning] Slave: Got error 0 'No error' from NDB Error_code: 1296
121027 16:09:40 [Warning] Slave: Got error 0 'No error' from NDBCLUSTER Error_code: 1296
121027 16:09:40 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.027069' position 11283558

"heartbeat" is just some table we have that gets updated once a second. I can run the query by hand while connected to the cluster's API node (the same node that is reading the bin logs). It seems that the failure only occurs when the query is run on the slave thread.

Also, the query itself succeeds -- the heartbeat table is updated with the replicated value of NOW().

I am running 5.5.27 ndb 7.2.8 on 64-bit Linux hosts. I have 4 data nodes, one management node, and one API node (for now). This same error happened with 5.5.22 ndb 7.2.6 -- I'd upgraded to see if that would fix the problem, but no luck.

I have dug around with mysqld-debug trace logs and gdb. Near as I can tell there's a problem writing to the ndb_apply_status table, but I cannot pinpoint exactly what the problem is (nor am I sure that I am on the right track there).

This issue is also being tracked on the MySQL Forums.

Ah ha. This seems to be the error you get when you try to replicate from a server that uses statement binlogs. I initially overlooked that this scenario was not supported.

I was able to work around this by adding an intermediate mysqld that just converts the statement replication binlogs into mixed replication binlogs (using log-slave-updates and binlog-format=MIXED).

Hope this helps someone in the future.


SQL Server 2016 Always On with Availability Groups: add instance on both servers I have a WSFC with SQL Server 2016 Enterprise Edition and 4 Availability Groups. Each server has 1 CPU with 6 cores @ 3.4GHz and 256GB of RAM.

Currently I have one instance on each, they serve 4 Availability Groups with a total of ~300 GB data in 95 databases.

I have the task of migrating more databases (35 databases, 235 GB data) into this environment, which I'm sure would be a breeze - had it not been for the fact that these databases come from a cluster with another collation. Hence, I'm thinking of adding an instance on each server and creating a separate Availability Group for this collation.

To help keep things sorted and sort of simple I will add an IP address on each server for the new instance. That way I can address both instances on port 1433 and won't need to sort that out in the availability Groups Creation - at least that's my thinking.
(I've used this approach on a single server with 3 instances - they all have their own Ip addresses which removes the need to address them by instance name - keeps it easy in case the db needs to migrate to another server. )

I found a similar question from a few years back but since there was no evident reason for the multiple instances the TS received a lot of whys, donts and whats. I my case I really need to have an extra instance due to the collation issue, my question to the Community is: is there anyone who could tell me "Yes" or "No"? And if either answer - could you please give me a good motivation?

If I can't add the instances I would have to go with another server, and due to the license costs of Enterprise Edition this would then be a non-clustered solution - reducing uptime, complicating patch routines, removing redundancy and so on. So: could I do this?

Each server has 1 CPU with 6 cores @ 3.4GHz and 256GB of RAM.
Currently I have one instance on each, they serve 4 Availability Groups with a total of ~300 GB data in 95 databases.
I have the task of migrating more databases (35 databases, 235 GB data) into this environment [...]

Considering there is a single socket with 6 cores and will hold 130 databases, that's already a good portion of available threads just for Always On usage and not counting anything else.

had it not been for the fact that these databases come from a cluster with another collation. Hence, I'm thinking of adding an instance on each server and creating a separate Availability Group for this collation.

Is it a requirement that the instance level collation be the same as the database collation? This is unclear.
Adding another instance is only going to make troubleshooting the inevitable issues you're going to have, that much harder as now you'll need to monitor both instances to see how one is impacting the other while the other is having issues.
I, personally, would not do this.

I found a similar question from a few years back but since there was no evident reason for the multiple instances the TS received a lot of whys, donts and whats. I my case I really need to have an extra instance due to the collation issue, my question to the Community is: is there anyone who could tell me "Yes" or "No"? And if either answer - could you please give me a good motivation?

You're throwing aside whatever has already been discussed and want an absolutist answer to an environment no one but yourself has access which is quite a hard question.
Instead of adding another instance, I'd add another set of nodes to the cluster or create a new cluster so that items were separate. It's still not clear if the requirement is instance level collation to match database level collation, so we're not even sure another instance is required.

If I can't add the instances I would have to go with another server, and due to the license costs of Enterprise Edition this would then be a non-clustered solution - reducing uptime, complicating patch routines, removing redundancy and so on.

Licensing aside, a stand alone instance would be simplified patching - using an AG complicates patching.
I'm afraid it's more or less down to two things:

Multiple Instance (if even required) are annoying beyond belief in terms of maintenance and troubleshooting. If you want to do this, then you need to be okay with knowing the investments of time and energy to troubleshoot small issues as BOTH instances would need to be investigated, mostly regardless of which was having the issue since one can easily affect the other.
The limited hardware, just 6 cores. Even if you added the databases to the current instance, the hardware just doesn't seem adequate. Since we aren't in your environment we don't know the current level of load on both servers, their current specs, and what this would do when migrated so it's really not possible for anyone to say. Having said that, it still doesn't make me feel good, considering there just isn't that much hardware available.


Connecting Linked Server Hi I want to fetch data from MS SQL DB of a Remote server using Linked Servers. I have linked the servers. I am fetching data as follows.

select * from [IP of server].[Database Name].[dbo].[Table Name]

But I want to get [IP address] from a variable and use it.

Ex: Declare @ServIP varchar(50)

set @ServIP = 'IP of server'

select * from [@ServIP].[Database Name].[dbo].[Table Name]

I'm getting error If I use variable.

How can I do it?

Dynamic SQL:

declare @ServIP varchar(50) = 'IP of server'
declare @dsql varchar(1024)
set @dsql = 'select * from ' + quotename(@ServIP) + '.DbName.dbo.TableName'
exec (@dsql)


How to set or grant SELECTINAUTH for a schema in DB2 11.5 I'm running DB2 11.5 and want to grant select, insert, update or delete rights for all tables in a specific schema. SELECTINAUTH etc. seems to be perfect for this.
When I'm looking to the SYSCAT.SCHEMAAUTH Catalog View I can see the following columns:

ALTERINAUTH
CREATEINAUTH
DROPINAUTH
SELECTINAUTH
INSERTINAUTH
UPDATEINAUTH
DELETEINAUTH

The first 3 privileges can be granted by e.g. GRANT ALTERINAUTH ON SCHEMA  TO USER . But this don't work for the last 4 columns.
The Knowledge Center tells that this are implicit privileges, but I can't find any hint, how a user or a group can get this privileges implicit or explicit.
Does anybody know when or how e.g. INYSERTINAUT is set to Y?

I got the following information from IBM: "So currently the usage of SELECTINAUTH, INSERTINAUTH, UPDATEINAUTH is only available in DB2 Warehouse" and "Schema authorization is one of these features and we have publicly stated (at IDUG conferences etc) that we will be brought to Db2 in an update to 11.5.".
It can be enabled with db2set DB2_ENABLE_SCHEMA_AUTHORIZATION=true, but it's not documented and therefore not supported yet. (as IBM explained).


Query where there are only two rows with specific data I have a table with room which has a data of who are in it. Each room has have 0 to any number of people and each person can be in multiple rooms simultaneously. (simplified below)

id      name

1       John
1       Peter
1       Jill
2       John    <--- room # I want
2       Jill    <--- room # I want
3       Someoneelse
...

If I want to look for a room where there is John and Jill and no one else, what would be an elegant & efficient way to select the room number?

Something like this works...

SELECT r1.id
FROM  `room` r1,  `room` r2
WHERE
    r1.name != r2.name
    AND r1.id = r2.id
    AND r1.name = 'John'
    AND r2.name = 'Jill'
    AND r1.id
    IN (
        SELECT id
        FROM user_conversation
        GROUP BY id
        HAVING COUNT( * ) = 2
    )

But I feel like there would be a much more efficient & elegant way to get this.

You don't need self-joining or subqueries at all. This query does the trick:

select
id
from
your_table t
group by id
having sum(name = 'John') + sum(name = 'Jill') = 2 and count(*) = 2;

see it working live in an sqlfiddle

name = 'whatever' in the sum() function returns 1 or 0, true or false.


Slow query compilation, lots of reads during compilation phase I have a query of the following form

exec sp_executesql N' UPDATE table  SET column1 = 1, modify_date=N''2020-02-12 04:55:59.000'' WHERE (column5=@P1           AND column6=@P2           )',N'@P1 nvarchar(36),@P2 int',N'458986156148',87

I cannot change anything about this query itself. It comes in from an application.

Every time it is executed the update values change, causing a new query plan to be generated rather than the previous plan being reused.

Forced parametrization is on but doesn't seem to have an effect on this query, probably by the way it is executed.

When executing the query itself I see a total of 4 reads for the update. In this case execution itself didn't cause an update as there are no rows returned for the where clause.

Using the profiler I see that the time between the query coming in and starting is almost the entire time of the query (1 second). Actual execution is almost immediate.

The profiler shows 455.000 reads!! which are not shown in the query execution itself.

So now I'm wondering

Can I force the query to use the same plan here? A plan guide seems only usable for the same query or for a query that is affected by forced parametrization.
Can we increase the compilation speed of this query? Where do the 455.000 reads come from? There are a lot of statistics on that table (+- 100) but 455.000 reads seems rather excessive.

This is on SQL Server 2019, no cumulative updates yet. I scanned the CU changelog for anything that might have to do with this.

edit/ Further investigation showed that there are a lot of locks on other tables during compilation time. I have 300 tables having a foreign key referencing the primary key in my table where I want to insert.

All foreign key relationships are trusted. Would there be any way to prevent these checks during compilation phase?

edit 2/ The dependencies are no foreign key constraints but views on the table. All views using that table have the SCH-S lock on them during execution which is expected. It's not clear if this is also causing the reads...

edit 3/ Apparently the 455.000 reads are done by scanning the sys.sysmultiobjrefs system table more than a million times. This doesn't seem proper behavior.

Forced parametrization is on but doesn't seem to have an effect on this query, probably by the way it is executed.

That's correct, forced paramterization doesn't work here because the query has "already been parameterized on the client-side application" (source).  In other words, the problem is that there are two actual parameters, so the embedded date and int parameters can't be forced.

On to what's going on with the reads / CPU...

Auto Created / Updated Stats

As far as the reads being shown in profiler, those do include statistics updates and creation.  That would explain the problem if it only happened occasionally.  Since you mentioned these long compile times and high reads occur on each execution, it doesn't seem like that's the culprit (unless this application is doing something really unusual, like aggressively deleting auto-stats).

Version Store Reads

There are other kinds of reads that are not reported in SET STATISTICS IO ON, which I talked about on my blog.  These include version store reads, so if you have SNAPSHOT or READ COMMITTED SNAPSHOT enabled on this database, that could explain some of the CPU / read discrepancy.  Especially if you have long-running transactions.

Additional Troubleshooting

It might be helpful if you could share an "Actual Execution Plan" for the query in question while reproducing the issue.  There could be clues in addition to the information you've already provided.

Plan Forcing

As far as I can tell, there isn't any way to prevent a plan from compiling each time one of these queries comes in (as you noted, plan guides won't work, and neither will Query Store plan forcing).

Ad-Hoc Workloads

As a side note, since you have these embedded parameters, your workload could probably benefit quite a bit from the optimize for ad hoc workloads server-level configuration option.  It will keep your plan cache from being bloated, which might also be contributing to CPU issues (as SQL Server has to evict items from the plan cache over time due to all the "new" plans coming in for each query).


Is there better way to 'touch' a row than performing a dummy update? I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update:

create table t(id integer);
insert into t(id) values(1);
insert into t(id) values(2);

select xmin::text from t where id=1
/*
|  XMIN |
---------
| 87159 |
*/

update t set id=id where id=1

select xmin::text from t where id=1
/*
|  XMIN |
---------
| 87196 |
*/

(SQL Fiddle)

I'm curious whether there is another way of bumping the xid without doing an update, analogous to the unix touch command?

Well, if you want to change the xmin, then it needs to be ensured that other transactions can still see the old version of the row, so you need to make a copy with the new xmin, which you do with an UPDATE.  You can reduce the impact of this by updating a non-indexed column, to get the benefit of HOT.

Of course, this whole line of thinking is dubious, and perhaps you should be using some other facility altogether.


Making a server with multiple instances a member of two effectively independent clusters We're in the early stages of planning a new database environment for our ERP and CRM applications (third-party). The goal is to use Always On Availability Groups for HA, rolling patches, read-only secondaries, etc. We want to use automatic failover, without the client application needing to be aware of the HA.

We've got one physical server that is quite massively specced. It's going to be running two instances of SQL Server: one for the ERP application that requires SQL Server 2016, and one for the CRM application that is better suited by 2017 (and this also allows us to give sysadmin rights to the CRM application vendor's support without affecting the ERP application).

We also have a pretty mature ESX infrastructure, and want to use some VMs as failover nodes. These won't be as decadently built as the primary server, but we will ensure acceptable performance for short outages or maintenance windows on the primary server.

My initial thought was to have two failover VMs: one for the ERP instance and one for CRM. It doesn't appear that a single server can be a member of multiple Windows failover clusters, i.e. I can't make an ERP cluster with the ERP VM and the physical server, plus a CRM cluster with the CRM VM and the same physical server.

If I were to instead build a three-node cluster, and control which nodes each instance can run on, would I be inviting any unpleasant side effects? My main fear is that quorum voting could get a bit weird if we start adding more database servers to the cluster, and we could have an instance go down unexpectedly even if all the nodes that can host that instance are still up. Is that even a risk, and are there reasonable ways to mitigate it? Are there enough possible complications that I should use a single, beefier VM that can handle both instances?

tl;dr:

Would I be shooting myself in the foot by building a three-node cluster where instance 1 can only run on servers A and B, and instance 2 can only run on servers A and C? Should I just build a two-node cluster (plus quorum member) and make sure server B can handle both SQL Server instances?

I cannot see any reasons as to why you would have any issues. Just be sure to add a file share or disk witness to the cluster, as that will ensure there is no "weirdness" in the quorum. As long as you set the AG's up correctly, I cannot see any reasons not to use your suggested approach.
The only negative i can think of, is that it will be an additional environment to manage and maintain, but you are already doing that having 2 instances on your primary. Will you be using the VM's for the Read-Only connections?


MySQL Proxy on Current User I'm trying to grant permissions to the current user logged in depending on their role, but it doesn't seem possible to have a select statement at the end of the GRANT PROXY # TO # command

The code I have for this is

CASE (SELECT RoleType FROM user)
WHEN 'standard' THEN
    GRANT PROXY ON 'standard_user'@'localhost' TO (SELECT CURRENT_USER())
WHEN 'admin' THEN
    GRANT PROXY ON 'admin_user'@'localhost' TO (SELECT 'Username' FROM user)@'%'
END CASE;

Does anyone have another idea on how I can achieve this?

I found the answer to be,

CASE (SELECT RoleType FROM user)
    WHEN 'standard' THEN
        GRANT PROXY ON 'standard_user'@'localhost' TO CURRENT_USER();
    WHEN 'admin' THEN
        GRANT PROXY ON 'admin_user'@'localhost' TO CURRENT_USER();
END CASE;


Snapshotting Database Tables I have a reporting database (in SQL Server) that takes nightly snapshots of the production data (Oracle) for users to query against for the next day, but now the requirement is getting to be that they want near realtime data (about every 15-30 mins). What is the best way to do this in production without downtime or any partial data getting returned?

I've looked into realtime data mirroring using triggers, but because of licensing we can't alter anything in the Oracle database.

I've seen some information on partition switching, where I do a full refresh into a set of alternate tables then switch the partitions. Is it good practice to be doing that on 20 tables every 15 minutes? A lot of these tables have one-to-many relationships, but I figure if I replace the "many" tables before the "one" tables, reports won't return partial data if they run during this process.

I also want to avoid locking the whole database, because I want the system to be able to scale to many concurrent users, and it seems like if I do that, even if it takes just a second, it might take some time to acquire the lock.

Any strategies or insights are appreciated! Thanks!

I've encountered this issue before and in previous cases for us the solution was to develop a system of ETL processes which bring the information over into the warehouse or reporting database. What we did was design the process to run near continuously and at the head of each iteration we would grab the current maximum datetime stamp of each table. We would then process each record that was generated or updated between the last timestamp and the current one we just retrieved then repeat this process constantly. There is a chance with this for partial data, but with the process running continuously any partial data would be resolved within a few minutes.


Will mysqldump command also backup all the views that are present in DB or there is a special command for it? I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it.

Please suggest me an answer. Any help is greatly appreciated.

There is no specific option for dumping views.

You can try the following:

mysqldump -h... -u... -p... --all-databases --routines --triggers --no-data > /root/MySQLDBSchema.sql
grep "CREATE ALGORITHM" /root/MySQLDBSchema.sql

You should be able to see the views. This indicates that when you dump databases, the view comes with it.

Another stunt you can try, just to get the views only, is this:

mysql -uroot -prootpass AN -e"select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views" | sed 's/;/\\G/g' | mysql --uroot -prootpass > /root/MySQLDBViews.sql

Give it a Try !!!


Storing user search keywords I need to store search keywords in a database to analyse user needs.
How efficient should my schema be?

I'm thinking like this:

CREATE TABLE SearchKeyword (
 ID         INTEGER        PRIMARY KEY AUTOINCREMENT NOT NULL,
 UserID     INTEGER,
 SearchKey  VARCHAR( 255 ) NOT NULL,
 Date       TIMESTAMP      NOT NULL
);

But with this, the table will grow. Any suggestions for improvements?

If the keys repeat frequently, create a second table

CREATE TABLE Key_tbl (key_id INT, key VARCHAR(<>))

and reference the first table with this, while inserting just look up the key and insert the key_id in SearchKeyword


Getting the first day of week In continuation of my previous question:
Get week/month data

I have a MySQL table with day column of type DATE and position column of type INT.

I need to fetch average position for every week in consideration. (A week is in consideration if there is a row with day value belonging to this week.)

The fetched data should be an array of two columns:

date
position

Here "date" is the first day (Sunday) of the week and "position" is the average position for this week.

The trouble is how to get the first day for a week.

I found a solution in comments of http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

SELECT DATE_ADD(day, INTERVAL (7 - DAYOFWEEK(day)) DAY) AS day, AVG(position) AS position FROM table


Looking for help reporting duplicates and null marks Someone over at StackOverflow suggested this might be the place to ask this.  I hope this will question is proper for this forum.  I am looking for suggestions / criticisms of alternative methods of finding errors in an Oracle table.  I am trying to report both duplicates and missing values something like this:

ID      MAINFIELD   LOCATION                            COUNTER
------- ---------   ---------------------------------   -------
16626   206000650   9A OLIVER ST CENTRAL STATION        2
18805   206000650   3 SWIFT CT CENTRAL STATION          2
22409   940000170   2 MARKET ST NEWARK DE               2
22003   940000170   1 MARKET ST NEWARK NJ               2
29533   970000030   95 MILL RD ANDOVER                  2
20256   970000030   12 RAILROAD AVE                     2
29018   978900050   44 BROAD STREET                     2
28432   978900050   WASHINGTON ST AND HAMILTON AVE      2
21831   980700050   BROADWAY NEWTOWN                    2
24147   980700050   MAIN STREET LEVITTOWN               2
26418                                                   3
26738               TEST DATA                           3
26755                                                   3

The ID is the primary key, is a number and not nullable.  The other columns are both NVARCHAR2 and nullable.  None of those are indexed.  I am not permitted to alter the tables or otherwise modify the database.  Some rows that I am reporting are outright errors.  Some are obvious typos.  Some appear to be test data.  The last three rows have a null MAINFIELD and there are three such rows (two of which have the location null also).

This was my original solution and produced what is shown above.
This appears to achieve the desired result:

SELECT a.id,
       a.mainfield,
       a.location,
       b.counter
FROM   maintable a
       INNER JOIN (
                    SELECT mainfield,
                            Count(*) counter
                    FROM   maintable
                    GROUP  BY mainfield
                    HAVING Count(mainfield) > 1 OR mainfield IS NULL
                  ) b ON a.mainfield = b.mainfield OR
                  ( a.mainfield IS NULL AND b.mainfield IS NULL )
ORDER  BY a.mainfield;

I found this to run more quickly:

SELECT a.id,
       a.mainfield,
       a.location,
       b.counter
FROM   maintable a
       INNER JOIN (
                    SELECT mainfield,
                            Count(*) counter
                    FROM   maintable
                    GROUP  BY mainfield
                    HAVING Count(mainfield) > 1 OR mainfield IS NULL
                  ) b ON NVL(a.mainfield,'***NULL***') = NVL(b.mainfield.'***NULL***')
ORDER  BY a.mainfield;

I liked this alternative, but it runs more slowly:

SELECT  id,
        mainfield,
        location,
        COUNT (id) OVER (PARTITION BY mainfield) counter
FROM    maintable a
WHERE   mainfield IS NULL
OR      EXISTS(SELECT 1 from maintable b
            WHERE mainfield = a.mainfield AND ROWID <> a.ROWID)
ORDER BY a.mainfield;

Is there a way to reference the counter [the COUNT (id) over PARTITION BY mainfield] in the WHERE clause which would allow me to speed this up?

I apologize in advance if this question is trivial and if there is some rule against cross-posting.  I am not particularly strong in this area.

Putting the following code in a response does not format it properly and this isn't so much an answer as a follow-up to the answer posted, so I am adding it here rather than in an "answer."

Is there any reason why I might want to structure the query like this instead:

WITH TempTable AS
    (
    SELECT  id,
        mainfield,
        location,
        COUNT(id) OVER (PARTITION BY mainfield) counter
    FROM    maintable
    ORDER BY mainfield
    )
SELECT  *
FROM    TempTable
WHERE   counter > 1 or mainfield is null
;

I like the syntax in the answer a bit better since it is easier to remember.  What I have above is something I was experimenting with after googling around the web.

Thanks.

Of course.

SELECT * FROM (
SELECT  id,
        mainfield,
        location,
        COUNT (id) OVER (PARTITION BY mainfield) counter
FROM    maintable a
) where counter > 1 or mainfield IS NULL
ORDER BY mainfield;


.bat file works manually, but does nothing in SQL Server Agent job I am able to run a .bat file that copies an .exe file into a specific folder, and runs it to extract some files manually.

When I use it as part of a SQL Server Agent job (step 1), I get success for the job called "Daily_Update", but it doesn't actually do anything.

The server that the SQL Server is on is different from where the file location where the files are moved and executed. I am specifying the file location with the UNC Path. \\A10 is the server with the scripts and .dat files that I am manipulating, \\A08 is the one with the SQL Server.

With the disk designator c:\ notation, I get:

  The system cannot find the path specified.

When I use the UNC path name, I get:

  Access is denied.

When I run the .bat file from the desktop of the server where the SQL Server lives, it works fine manually.

Any ideas why this might be happening?

The SQL Server user account has access to that specific folder?
Try running a simple xp_cmdshell to check that.

EXEC master.dbo.xp_cmdshell 'dir C:\<path>'


Prefix matching while importing large CSV file I have list of huge phone numbers to be imported to the database. And want to group each phone number by their states and timezones of the state, by identifying them from their prefix. Timezones are Eastern, Central, Pacific etc .. For example 786800XXXX is a number from Florida which is in Eastern timezone. I thought of doing this via a trigger where I check the first three digits of each number and then put them to matched state. Something like SELECT LEFT(NEW.contact , 3) INTO vAreaCode; and then
SET NEW.timezone = vTimezone; This probably is going to be resource hog on a busy server. What would be the best way of achieving this task?

The reference table look like

CREATE TABLE `timezones` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `prefix` varchar(11) DEFAULT NULL,
  `state` varchar(11) DEFAULT NULL,
  `timezone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

id   prefix  state   timezone
 1   786     Florida    EDT
 2   213     California PST

And my contacts table look like

CREATE TABLE `contacts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `contact` varchar(11) DEFAULT NULL,
  `timezone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

id   contact   timezone
 1   2147483647 EDT
 2   2248918882 CST

DRY -- a database principle -- "Don't Repeat Yourself".

Instead of copy the data over as you load it, look it up when you need it.

SELECT c.contact, tz.state, tz.timezone
    FROM contacts AS c
    JOIN timezones AS tz  ON tx.prefix = LEFT(c.contact, 3)

Caveat:  This assumes that you won't modify the timezone after assigning it to a contact.

What is the application?  Perhaps some form of call center?


Normalizing nearly identical tables Background

I'm managing a relatively small database project in which we are adding support for reporting involving status updates for items in out product table. I was bit thrown into this and I've only got about a month of ever writing SQL.

Problem Description
At it's core we have a central table [product] with a bigint unique key. We now want to record various messages that come in from a satellite application. The messages come in 2 major types (MessageA and MessageB) that are almost identical, MessageB contains an extra column that MessageA doesn't posses. Also of note is that there is no overlap between the 2 message_type columns and no columns are NULL. That is to say both messages have their own set of message_types.

MessageA:

id
timestamp
message_type
product_id
floor_id

MessageB:

id
timestamp
message_type
product_id
floor_id
section_number

What I tried
My initial design was to add 2 tables, one for each new data type exactly mirroring the datatypes. This "seemed" more "normalized" based off my month of so of SQL experience. But after I started writing a query that tried to combine the data into a report, I couldn't come up with a non-redundant query to build the dataset. My primitive query looked like:

Pseudocode

(
   SELECT MessageA.* FROM product
   WHERE <filtering crieteria on product>
   JOIN MessageA ON MessageA.product_id = product.id
)
UNION ALL
(
   SELECT MessageB.* FROM product
   WHERE <identical to first filter>
   JOIN MessageB ON MessageB.product_id = product.id
)

I'm a little paranoid about the long-term performance implications of querying [product] twice since it's our biggest table (adds up to 1M rows a year, maybe more) and the DB runs largely unmaintained off-site on consumer level hardware for an average life-cycle of 3-5 years between upgrades and we have had some reports trickle in of issue at out largest sites. These new 2 tables would potentially grow at 3-7 times the rate of [product] (possibly 5 million rows per year or more).

I started to think it might be simpler to just have 1 table and make section_number NULL. If section_number = NULL then it is or type A otherwise it is B.

 The actual question
Is this a good idea?

Should I be worrying about this optimization?

Is this even an optimization or just a more accommodating design?

I'm looking for some guidance whether I should shape the data based on "input" format or "output". Normalization is elegant but at what point should I bend the schema to look like the desired output structure?

There are two ways of approaching the answer to your question:

First: Is pre-optimization a good idea?

As a general rule, don't pre-optimize on the assumption that you will have a problem.  Use volume testing to determine if you have a problem and denormalize for optimization purposes if that is the best of your available solutions/compromises.

Second: Is this a good case for denormalization?

Having said that, there is a practical limit to how much you want to be fussy about functional dependencies.  Are your type A and type B messages really that different?  They both seem to quack like a duck, as it were.  Having only a single attribute different, and that difference being whether it is null for one set of records and not null for another set of records isn't necessarily a good reason to implement two distinct message tables.

You might want to have a logical model that makes the distinction between type A and type B messages, but it doesn't necessarily follow that your physical model has to implement these two entity-types as separate tables.

You have the option of using a constraint to enforce the relationship between message type and section number.  You don't have to implement your constraint through normalization.


Row level security in SQL Server 2016 with a normalised schema SQL Server 2016 supports row level security, which is great. However all of the samples assume that the username is inside the table you wish to apply security to (see example below- from MSDN https://msdn.microsoft.com/en-us/library/dn765131.aspx).

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);

If I had a more normalised schema, my username would be in a separate table, using an ID as a foreign key. In this situation, how would I use row level security to check for a user name? Can I add a join to the filter predicate?

The function can have table access, just remember that this will add additional overhead to every single query.

CREATE FUNCTION Security.fn_securitypredicate(@SalesRepID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
      FROM dbo.SalesReps AS sr
      WHERE sr.SalesRepID = @SalesRepID
       AND (sr.name = USER_NAME() OR sr.name = 'Manager');
GO


Backup Certificate SQL Server and return bytes with a Select Statement I have a certificate in a database in Microsoft SQL Server 2008 R2. I want to get a copy of the certificate so I can burn it onto a USB device and put it in a safe. I don't want to have to go onto the sql server machine itself, I want to do all of this from my client application which I have written.

I know how to backup a certificate using BACKUP CERTIFICATE (http://msdn.microsoft.com/en-us/library/ms178578.aspx) but this only has options to save to a FILE.

Is there another technique (perhaps using sys.certificates) which I can use?

We are planning an upgrade to 2014, so if there is functionality specific to that version I can use that, but ideally the solution should work with 2008R2.

New in SQL Server 2012 and also in 2014 are two new encryption functions that will return a binary stream as you are describing. CREATE CERTIFICATE was also updated to take BINARY input for public and private keys.

CERTENCODED will return the public key portion of the certificate in BINARY form.
CERTPRIVATEKEY will return the private key portion of the certificate in BINARY form. this is encrypted so the password to decrypt it would also be needed.

The above could be saved in a binary form and written to a disk, or stored in textual representation of the binary form and written to disk.

Should you need to create it again, use the BINARY options of the CREATE CERTIFICATE function.

Edit: There is nothing in 2008R2 that can do the same that I know of through system functions.


Can i Make Index for all columns(No write operations)? Few days I just learned indexes.I my New app(running on node js) There will no write operation.And my server has 500mb of ram.Can I set Index to all of My column.I almost have 500K rows.My query is dead simple as Like this

SELECT * FROM users ORDER BY RAND() LIMIT 1

Limit may vary upto 10k According to users need.Right Now I am getting 0.6s In my 8Gb ram laptop.What would Happen if it is on 500Mb ram Server.Is it worth for Making Indexes in all table

This all depends on the size of the table. You're doing a full table scan without a where clause so an index isn't going to help. The use of the rand() function for sorting is going to produce a temp table and this will harm performance. If you have a primary key you could generate a random number in the app and perform a query where a single row is selected and can make use of the clustered index. You're likely to be serving this all from memory if you're using the InnoDB storage engine.


Table design question for a `Check all that apply` survey I'm looking for advice on a design for a portion of a table.

Background

We're collecting survey data for multiple sites, and one question is a check all that apply. Currently, there are from 2-5 available options based on the site, though that number could increase. When I get the data, I get anywhere from 2-5 columns (based on the number of options available on that survey), each column in the result set responds to one of the options. The data in the columns will all be either NULL or a text value representing that ordinal option for that particular survey. Different sites will also have different options from a set, so there is no guarantee that column 1 is always the same response. There are 13 total possible options to pick, at this point, and no one site has more than 5 available, but it's possible that we'll add more options to pick from, and that the there could be more than 5 options per site in the future.

Sample Data

Survey 1:

Col 1       Col 2     Col 3
A           B
            B
A                     C
A           B         C

Survey 2:

Col 1       Col 2     Col 3
B           D
            D         E
                      E
B

Survey 3:

Col 1       Col 2     Col 3
A           D
            D         F
A                     F
A

The question

How do I best store this data?

Currently

I get the data in a .CSV, and for expediency & urgent deadlines, I've been pasting it into an Excel spreadsheet. (That's why I'm looking to move it to an actual table...) I've created one column for each possible answer, and if the value in CSV.Col1 = Excel.TableHeader, then I put a 1 in that column. This allows me to quickly filter the Excel rows and sum the 1s for a quick total of how many there were. This is probably not ideal for an actual database.

Possibilities Considered

I've considered assigning each of the 13 options a value and summing them to store them in a single column. For Example:

A = 1
B = 2
C = 4
D = 8
So my Survey 1 example from above would store the values of:
3
2
5
6

I've considered leaving it as it is currently with 13 columns of either Boolean, Number, or Text format. Each column would hold [True|False], [1|0], or [A|B|C|D...], as appropriate.
Creating a 1:M dependent table to store a result per row, linked to the master table row for that survey. The child table would have a PK, a Foreign Key back to the parent table, and a text column to hold the response.

The back end

The data will be moved from Excel to Access, with the intent to move it into SQL Server as soon as possible.

Option 3 is classic 3NF design.  If you need to add data to the table quickly, it will likely be the highest performing approach for inserts, so if you need to insert the data quickly and have high concurrency concerns that may be the way to go.

To me, it sounds like you're trying to solve an analytical problem.  If that is the case, I'd go with option 2.  This is a de-normalized approach.    Your users are likely already thinking about their data in that way, so they'll appreciate the simplicity in it.  With the way that you're receiving your data, it could be the most difficult to achieve, but the effort to get it in simply will be worth it.  Your users will appreciate the elegance which is hopefully your goal.

Good Luck!


create a related row if no related row exists I have two mysql tables with a one-to-many relationship.

I want to find all rows in the 'one' table which do not have a row in the 'many' table and add a single row in the 'many' table for each one.

How can I edit my query to achieve this?

INSERT INTO many_table SET
    id = ( SELECT id
           FROM one_table
           JOIN many_table ON one_table.id = many_table.related_id
           WHERE many_table.related_id is null
         )
    name = 'george'

Not sure what is name = 'george' is doing but assume it's part of the filtering in WHERE clause.

INSERT INTO many_table (id, name)
SELECT o.id, o.name
FROM one_table o
LEFT JOIN many_table m ON m.related_id = o.id
WHERE o.name = 'george' AND m.id IS NULL


Postgres backup and WAL to S3 We are looking for a solution for the following problem:

We have set up streaming replication so we have a master DB and a slave DB, we want to have basebackups and WAL files sent to our S3 storage periodically.

I have looked at:

WAL-e which seemed to suit us perfectly, except that it does not support multiple tablespaces, which is a deal breaker.
Barman supports this but does not support S3, which is also a deal breaker, although it may be possible with pre- and post-backup hooks which I am currently looking into.
OmniPITR doesn't support S3 either.

Is there an application that does all these things already, or will I have to do some scripting?

After a lot of research, I ended up using Barman and a custom post-backup script, it works very good!


How to store external IDs from different sources? I have a table of customers with a PK that we can use as an identifier for our internal system. These customers may belong to different companies, each of which may have an external ID in some arbitrary structure to identify the customer. Furthermore, customers and companies have a many to many relationship.

My dilemma is whether I should store the external ID in the linking table itself or via some external table (because the external IDs may be of arbitrary form). We need to be able to find customers for a particular company via internal or external ID.

The fact that the ExternalID is in an arbitrary format should not impact the design of the schema. As it's a many to many relationship, you can use the following:

Customer
- ID
- Name
- etc...

CustomerCompany
- CustomerID (PK, FK to Customer)
- ExternalID
- CompanyID (PK, FK to Company)

Company
- ID
- Name
- etc...

Depending on the business logic, it may also make sense to include ExternalID in the primary key for the CustomerCompany table.


Execute a T-SQL job with a different connection string Is it possible to execute a stored procedure from a SQL agent job while specifying a custom Application Name, similar to specifying the Application Name in a connection string?

The reason I want to do this is is to a avoid a trigger on the database that specifically distinguished on the Application Name used by the connection string.

I know you can execute an integration package with a different connection string, but I hope this is possible for T-sql jobs as well.

I don't think there's a built-in way to do this, but here are a few alternatives:

Use OPENDATASOURCE like this:

EXEC OPENDATASOURCE('SQLNCLI', 'Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=master;Application Name="test app"')
    .master.sys.sp_configure;

Note that using OPENDATASOURCE requires you to turn on the Ad hoc Distributed Queries sp_configure setting (as noted in the Remarks section of the documentation) even though you won't be writing code that accesses another server (SQL Server doesn't know that).
Create a wrapper SSIS package that specifies the application name in a Connection Manager, and then uses an Execute SQL task to run the stored procedure.
Create a wrapper command-line app that takes a connection string and a command as parameters; execute it using an Operating System (cmdexec) job step.


Is it possible to extract (not reset) the sa or other passwords from SQL Server? Let me start out that I do not need to reset the SA password, I know how to do that and there are plenty of resources on this site and elsewhere that give detailed step by step tutorials.

We have SQL servers set up on site at clients premises, the problem I am having is 3rd party companies are logging in to the server using our own service logins and doing data mining and data pushing. We are trying to stop this behavior (I know this is a impossible goal, but I am trying to convince other people of this fact).

We have figured out how they are getting our logins currently and will be fixing that, however what I would like to know is: Is there any known tool or process that can "extract" login passwords from a sql server instance (be it a offline attack or via intercepting and snooping a valid connection)?

I think there is likely a way to do it but management would like proof that it can be done.

Does anyone know of any set of tools or processes that it can be done with so I can demonstrate a proof of concept to management?

Depending on access rights to the server and the context in which it operates, there are a variety of ways someone might surreptitiously obtain a SQL username and password.

For example, if a user:

Has rights to attach a debugger to the client application or to the SQL Server process, they could read the password when it’s in memory.
Is able to temporarily redirect the client application away from the SQL Server (e.g. by editing DNS/hosts file, editing a SQL alias, temporarily stopping the SQL Server process then capturing the port used by that process, etc.), they could write a simple program that mimics TDS’s prelogin and login stages, have the client application connect to it, then walk away with the SQL authentication credentials used by the client application.
Is able to access the SQL login’s password hash (e.g. they have rights to sys.sql_logins), they could pull off a brute force attack using SQL queries (e.g. using PWDCOMPARE). At first, this may seem to require extraordinary computing resources and so be unlikely to succeed. However, if the user has some knowledge about the password (e.g. they’ve watched it being typed in and determined that it’s exactly 10 characters long and that only one letter is capital), the amount of effort required for them to do a brute force attack to crack the password is drastically reduced.
Has the private key to SQL Server’s SSL certificate, they potentially could intercept credentials by using a wire sniffer to decrypt TDS's login message.


SqlDataAdapter vs SqlDataReader for DBAs Speaking to developers about the use of SqlDataAdapter vs SqlDataReader and reading this: https://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader

Yields little in the way of explaining the effect on SQL Server of switching from SqlDataAdapter to SqlDataReader.

I understand SqlDataReader potentially increases the length of time a connection is open to SQL Server, and that certainly is a concern when it comes to efficient use of pooled connections.

Aside from this, what is the effect on SQL Server of using SqlDataReader instead of SqlDataAdapter? Has anyone observed the effect of this change on SQL Server?

In my experience, I think of SqlDataAdapter sort of like a .net SELECT * INTO #TEMP except the #TEMP is on the client computer.  The entire result set is read from SQL Server and that connection is no longer needed.  From a SQL Server perspective, the query comes in, runs and gets out.  From a client perspective, the entire result set is loaded into memory on the client computer. This can be extremely problematic when reading large amounts of data.

On the other hand, I think of SqlDataReader as a cursor mechanism whereby a SQL Server connection is opened and the SqlDataReader serves up one row at a time.  The application might do a bunch of work between each read and that can cause the connection to SQL Server to stay open for an extended amount of time.  This technique can allow the application to process a large result set without having to cache the entire set of data on the client computer.


How to find error rows in bulk insert? MySQL 5.6: I'm experimenting with copying data from one table to another in different ways. The source table is on a remote server and has about 500,000 rows - I use the federated engine to connect. I first tried this:
mysql > create table tgt as select * from src;

This is very fast, takes only a few seconds, but it gives warnings:
...
| Warning | 1299 | Invalid TIMESTAMP value in column 'created_timestamp' at row 265975 |
| Warning | 1299 | Invalid TIMESTAMP value in column 'created_timestamp' at row 265976 |
...
64 rows in set (0.00 sec)

I tried instead to do it with a stored procedure, opening a cursor, fetching rows and inserting them, but it takes forever; I canceled after 10 min.
So, is there a way to locate the rows that cause the problem? I tried select ... limit #first_row,#last_row;, but it doesn't appear to work, and I'm not sure if it is entirely reliable any way.

To locate the rows you want to see, you must do two things:

change the way you create the table
change the way you load data into the table

Why Change the Way You Create the Table ???
When you did this
create table tgt as select * from src;

You create the tgt table without any indexes. You can verify this by running
show create table src \G
show create table tgt \G

and you will see src with its indexes and tgt without them.
Consequently, with no indexes around, the table load would be the fastest. If you at least had a primary key. you could navigate the table by some numeric id column.
So, to create table with the indexes in place, do this:
create table tgt like src;

You could then run
show create table src \G
show create table tgt \G

and see that they are the same in structure.
Why Change the Way You Load Data into the Table ???
You could just start loading like this
insert into tgt select * from src;

When the load is done, you could run
SELECT * FROM tgt WHERE id = 265975;
SELECT * FROM tgt WHERE id = 265976;

You could get an glimpse at when the row looks like but the created_timestamp may not be populated. If it is populated correctly, then the problem is essentially solved.
Another Way to See Which Row Without Loading Data
You could isolate which rows have an issue with the created_timestamp by doing something else
# mysqldump --skip-extended-insert mydb src > dumpfile.txt
# head -265976 dumpfile.txt | tail -2

This will show you the rows that would have been inserted.
This is all the help I can suggest since I do not know what the table structures are.


Transaction atomicity implies consistency Transactions are said to need both atomicity and consistency. But, what is the point of consistency?

Once you ensure that you either update both accounts on money transfer, you are consistent. Wikipedia article on consistency says that consistency is a set of programmatic constraints, invariants. But you are free to code your transaction operations any way. So, I feel that programmer only needs Atomicity, Isolation and Durability. Consistency is an additional convenience DB tool, like trigger or stored procedure and is not absolute low level requirement. Given AID, the programmer can always ensure the consistency. It is a simple as check user data and reject the request if some rule is broken. Right?

It looks like Wikipedia identifies Atomicity with Consistency in the Data Consistency article, but not in the article on ACID transactions.

Listening on eventual consistency, I see that developers mean that DB is always in some correct snapshot state, i.e. data are stabilized and you can query/issue your transaction upon the DB, which you could not do if DB is available in transition. That is, consistent DB is one which satisfies the constraints at any moment of time. But, it satisifies the constraints if operations are atomic. Atomicity implies that we see only transaction results, which implies that we see only snapshots, which means consistence. Why do you need to single the consistence out as if it is independent property?

Transaction atomicity implies consistency

This is not true. A transaction that adds an order for a nonexistent customer is atomic, but makes data inconsistent.

To follow your example, a consistent bank transaction must guarantee that the sum of all debits and credits is exactly 0. An atomic transaction must only guarantee that either all necessary tables, or none, are updated, but says nothing about the values they are updated with.

Atomicity is one of tools, but not the only one, used to ensure data consistency.