Unique Constraint with Multiple NULLs

Microsoft SQL Server 2008 introduces filtered indexes, allowing you to create an index on a subset of rows based on a predicate: CREATE INDEX ... WHERE ... . There are many interesting tuning aspects of this new capability that deserve detailed discussion, but here I just wanted to provide a small tip regarding this new feature.

Related: Passing NULL Parameters

I started playing with filtered indexes and realized that you can create unique ones. It suddenly dawned on me that a filtered index can be used to address a very old common request -- to create a unique constraint that supports multiple NULLs. ANSI SQL supports two kinds of UNIQUE constraints -- one that enforces uniqueness of NULLs just like with known values, and another that enforces uniqueness of known values but allows multiple NULLs. Microsoft SQL Server implemented only the former. But every so often someone asks if there's a way to provide an alternative in Microsoft SQL Server, and the common suggestions are to use a trigger or an indexed view.

In Microsoft SQL Server 2008 you can use a very elegant and efficient solution -- define a unique filtered index based on a predicate that excludes NULLs. As an example, consider the following table definition:

CREATE TABLE dbo.T1(col1 INT NULL, col2 VARCHAR(10) NOT NULL);

Suppose that you want to enforce a unique constraint with multiple NULLs on col1. You can achieve this with the following index definition:

CREATE UNIQUE NONCLUSTERED INDEX idx_col1_notnullON dbo.T1(col1)WHERE col1 IS NOT NULL;

To test the index, try to insert two rows with the same known col1 value:

INSERT INTO dbo.T1(col1, col2) VALUES(1, 'a');INSERT INTO dbo.T1(col1, col2) VALUES(1, 'a');

The second INSERT fails. On the other hand, you will have no problem inserting multiple rows with NULL in col1:

INSERT INTO dbo.T1(col1, col2) VALUES(NULL, 'a');INSERT INTO dbo.T1(col1, col2) VALUES(NULL, 'a');

Cheers,
--
BG