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

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

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

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