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

T-SQL
1
2
3
4
5
6
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)
)
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

T-SQL
1
2
3
INSERT TestUnique VALUES(1)
INSERT TestUnique VALUES(null)
INSERT TestUnique VALUES(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

T-SQL
1
INSERT TestUnique VALUES(1)
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

T-SQL
1
2
SELECT ID from TestUnique
WHERE ID =1
SELECT ID from TestUnique
WHERE ID =1

This will return the rows with the value NULL

T-SQL
1
2
SELECT ID from TestUnique
WHERE ID IS NULL
SELECT ID from TestUnique
WHERE ID IS NULL

Now what do you think this will return? :-)

T-SQL
1
2
SELECT ID from TestUnique
WHERE ID <>1
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

T-SQL
1
DROP TABLE TestUnique
DROP TABLE TestUnique

The second way we can add an index with multiple NULL values is by using a filtered index. I already covered filtered indexes in this post Filtered Indexes as part of the SQL Advent 2011 calendar

Let’s see how we can do this. Create the unique table again

T-SQL
1
CREATE TABLE TestUnique (Id int)
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.

T-SQL
1
2
CREATE UNIQUE INDEX SomeIndex ON TESTUNIQUE (ID)
WHERE ID IS NOT NULL;
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

T-SQL
1
2
3
INSERT INTO TestUnique VALUES(1);
INSERT INTO TestUnique VALUES(null);
INSERT INTO TestUnique VALUES(null);
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

T-SQL
1
INSERT INTO TestUnique VALUES(1);
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

T-SQL
1
SELECT * FROM TestUnique;
SELECT * FROM TestUnique;

Here are the results
Id
—–
1
NULL
NULL

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