In yesterday’s post Unique index difference between Oracle and SQL Server , I showed you that SQL Server only allows one NULL value in an unique index while Oracle allows multiple NULL values. Today we are going to look how we can allow multiple NULL values as well in a SQL Server unique index. I am going to show you two techniques. The first technique is known as a nullbuster, this was first demonstrated I believe by former SQl Server MVP Steve Kass. Basically you use a computed column to allow for multiple NULLs. Here is an example
CREATE TABLE TestUnique ( pk int identity(1,1) primary key, ID int NULL, nullbuster as (CASE WHEN ID IS NULL THEN pk ELSE 0 END), CONSTRAINT uc_TestUnique UNIQUE (ID,nullbuster) )
Now insert 3 rows, two of them being NULL
INSERT TestUnique VALUES(1) INSERT TestUnique VALUES(null) INSERT TestUnique VALUES(null)
That worked without a problem. Now let’s insert the value 1 again
INSERT TestUnique VALUES(1)
As expected that blows up
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint ‘uc_TestUnique’. Cannot insert duplicate key in object ‘dbo.TestUnique’. The duplicate key value is (1, 0).
The statement has been terminated.
This will return the row with the value 1
SELECT ID from TestUnique WHERE ID =1
This will return the rows with the value NULL
SELECT ID from TestUnique WHERE ID IS NULL
Now what do you think this will return? 🙂
SELECT ID from TestUnique WHERE ID <>1
If you want to know why, look at number 7 here: SQL Server Quiz, Can You Answer All These?
Drop the table
DROP TABLE TestUnique
Let’s see how we can do this. Create the unique table again
CREATE TABLE TestUnique (Id int)
Here is how you create the filtere index, it is pretty much a regular index with an additional WHERE clause.
CREATE UNIQUE INDEX SomeIndex ON TESTUNIQUE (ID) WHERE ID IS NOT NULL;
What we are telling SQL Server is to index everything that is not NULL
Insert these 3 rows
INSERT INTO TestUnique VALUES(1); INSERT INTO TestUnique VALUES(null); INSERT INTO TestUnique VALUES(null);
If you try to insert a value of 1 again, you will get an error
INSERT INTO TestUnique VALUES(1);
_Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.TestUnique’ with unique index ‘SomeIndex’. The duplicate key value is (1).
The statement has been terminated._
Now let’s select from the table
SELECT * FROM TestUnique;
Here are the results
There are two rows with the value NULL in the result set. As you can see using a filtered index enables you to create an index which mimics the behavior from a unique index in Oracle