Sql-server – NULL value self comparison in a table

sql serversql-server-2012t-sql

I am always puzzled regarding some mysterious t-sql behavior, like the following

-- Create table t and insert values.  
use tempdb
CREATE TABLE dbo.t (a INT NULL);  
-- insert 3 values
INSERT INTO dbo.t values (NULL),(0),(1);  
GO  
set ansi_nulls off -- purposely turn off, so we can allow NULL comparison, such as null = null
go
-- expect 3 rows returned but only 2 returned (without null value row)
select * from dbo.t where a = a

This is not about how to retrieve all rows in a table and also not about avoiding use of ANSI_NULLS.

I just want to solicit some insights why t-sql behaves like this.

Best Answer

This is a surprising behaviour but from MSDN page, SET ANSI_NULLS, we can at least know that is the expected behaviour. One more reason to never use ANSI_NULLS OFF:

SET ANSI_NULLS affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

Related Solutions

Sql-server – Specific TempDB insert of UserDB select results in SOS_SCHEDULER_YIELD to ENCRYPTION_SCAN

You won't only see ENCRYPTION_SCAN resource in your wait list when Encryption (like TDE) is used.

Certain operations will take a shared lock on this resource to make sure the database is not being encrypted during the operation.

The moment you would encrypt a user database with TDE, the tempdb will also be encrypted (otherwise, you would have security risk when User data is used in temp db).

Therefore, some operations will take a shared lock on ENCRYPTION_SCAN in Tempdb to prevent Tempdb from getting encrypted.

Here are two examples:

BULK INSERT

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

--create temp table to hold numbers
create table ##NumberCreation (C int NOT NULL);
GO

-- CREATE Numbers by using trick from Itzik -> http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers 
WITH L1 AS ( SELECT 1 as C UNION SELECT 0 ),
    L2 AS ( SELECT 1 as C FROM L1 CROSS JOIN L1 as B ),
    L3 AS ( SELECT 1 as C FROM L2 CROSS JOIN L2 as B ),
    L4 AS ( SELECT 1 as C FROM L3 CROSS JOIN L3 as B ),
    L5 AS ( SELECT 1 as C FROM L4 CROSS JOIN L4 as B ),
    L6 AS ( SELECT 1 as C FROM L5 CROSS JOIN L5 as B),
    Nums as (SELECT ROW_NUMBER() OVER (ORDER BY C) as C FROM L6) 
insert ##NumberCreation(C)
SELECT TOP 500000 C
FROM Nums

The above code will generate 500k records in a global temp table, you can export these with the following commands. If you run this from SSMS, make sure you are in SQLCMD mode:

--Export
!!bcp ##NumberCreation out "E:\SQLServer\Backup\test\export.dat" -T -n

--format file
!!bcp ##NumberCreation format nul -T -n  -f "E:\SQLServer\Backup\test\export.fmt"

Make sure to choose a directory where SQL Server service account has write permissions and if you run this from SSMS, run it locally on the SQL Server.

Next thing is to start a bulk insert loop. While the loop is running, open a second screen and start running sp_lock untill you see the ENCRYPTION_SCAN shared lock in DB_ID 2 (Which is Tempdb).

The bulk import loop:

BEGIN
    IF OBJECT_ID('tempdb..#Import') IS NOT NULL
        DROP TABLE #Import ;

    CREATE TABLE #Import (C INT) ;
    BULK INSERT #Import
    FROM 'E:\SQLServer\Backup\test\export.dat' WITH (FORMATFILE='E:\SQLServer\Backup\test\export.fmt', FIRSTROW=1, TABLOCK) ;
END
GO 500 --run it 500 times

See the result of sp_lock in second window:

enter image description here

SORT IN TEMPDB

With the same Temp table in place start this very simple loop:

SELECT * from #Import order by C
go 50

It will produce the following Execution plan:

enter image description here

(Make sure that #Import is actually populated, since depending on when you stopped the previous bulk import loop, it could be empty!)

Again, run sp_lock in a second window until you see the ENCRYPTION_SCAN Resource popping up:

enter image description here

Now you know, why this resource wait is showing up. It could be very well that this is not your problem. I'd just wanted to point out the other reasons that make ENCRYPTION_SCAN show up. The reason for your query slowdown might be something else. I'll leave improving your query plan up to the query plan experts on this site ;-) However, could you post the actual execution plan as well instead of just the estimated plan?

Sql-server – Updating large table worked before, now taking hours

It might help a lot if you would have listed the indexes you have or the query plan. If the columns 4, 5 or 6 are indexed, that can slow the process down, and having a clustered index on columns 1, 2 and 3 should speed it up.

If columns 4,5 or 6 are variable length (e.g. varchar) then updating new value can cause a lot of page splits, that definitely will make it slower.

I think what John M meant was that if there's a reasonable amount of distinct values in for example column1, you could first collect the distinct into a temp. table and then run the update separately for each of the values because doing the joining the tables in smaller parts is usually a lot faster.

Of course everything depends on several things, like data types, number of distinct values in the columns 1-3 etc.

Best Answer

Related Solutions

Sql-server – Specific TempDB insert of UserDB select results in SOS_SCHEDULER_YIELD to ENCRYPTION_SCAN

Sql-server – Updating large table worked before, now taking hours

Related Question