UNIQUE INDEX vs multiple NULL values

In Microsoft SQL Server, if you want to make sure that a column contains no duplicates, you can use the UNIQUE keyword to enforce this constraint, either by creating a unique index or by adding a unique constraint to the column. It’s very useful and nice, but… What about NULL values? I mean, obviously, you can insert the first NULL value, but what if you have more null values and you need to enforce only the uniqueness of the non-null values? (eg. you have to store social security numbers but you have people working in other countries with no SSN) The ANSI standards says that the non-null values should be unique, however, the MSSQL requires all value to be unique, that is, you can have only one null in a unique column. The first approach says that NULL can’t be equal to NULL by definition, so I can handle any number of NULL values. The second approach says that you can’t insert a value into the column which is already there, and I can find a NULL among the values… Whatever, the point is that it can be quite bothering, or to be precise, it could have been bothering as in SQL2008 you have an almost nice workaround for it. The filtered index is a special kind of index which contains the keys for only a subset of the table, filtered by a WHERE clause. So you can create a filtered index this way:

CREATE INDEX ix_filtered ON dbo.MyTable (col1) where col2 > 55

Or if you want to use it to remediate your unique index with multiple NULL values problem, it will look like this:

CREATE INDEX ix_filtered ON dbo.MyTable (col1) where col1 is not null

A short demo:

CREATE TABLE tempdb.dbo.t1 (col1 int)
CREATE UNIQUE INDEX IX_t1_col1 on tempdb.dbo.t1 (col1)
WHERE col1 IS NOT NULL
-- insert a few rows...
INSERT INTO tempdb.dbo.t1 VALUES (1),(2),(NULL)
--this will fail...
INSERT INTO tempdb.dbo.t1 VALUES (1)
-- but this won't...
INSERT INTO tempdb.dbo.t1 VALUES (NULL)

Here you go. It’s quite quick-and-not-so-dirty. :)

2 Comments

  1. Rollback » Blog Archive » UNIQUE és több NULL:

    [...] article has an English version…) Ha szeretnénk biztosítani azt, hogy egy oszlop értékei mind egyeidek legyenek, [...]

  2. Hung:

    Cool, it’s exactly what I’m looking for… (unfortunately, this feature is not available for SQL 2005)

Leave a comment