How to create a unique index on a NULL column?

I am using SQL Server 2005. I want to constrain the values in a column to be unique, while allowing NULLS.

My current solution involves a unique index on a view like so:

CREATE VIEW vw_unq WITH SCHEMABINDING AS
    SELECT Column1
      FROM MyTable
     WHERE Column1 IS NOT NULL

CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column1)

Any better ideas?

Using SQL Server 2008, you can create a filtered index: http://msdn.microsoft.com/en-us/library/cc280372.aspx. (I see Simon added this as a comment, but thought it deserved its own answer as the comment is easily missed.)

Another option is a trigger to check uniqueness, but this could affect performance.

The calculated column trick is widely known as a "nullbuster"; my notes credit Steve Kass:

CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X  int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)

How to create a unique index on a NULL column?

Related

Recent Posts