I came accross a situation where I needed to temporarily insert multiple rows in a table with a Null value in a column with a UNIQUE constraint on it. In other words, I wanted the values in that column to be unique, except when they are Null. According to ANSI SQL specifications, this should be possible, but SQL Server does not allow this. I found a solution on another blog, which I like for its simplicity.

I am reposting the code here just in case that ohter blog post ever disappears:

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)
)