Why does a UNIQUE constraint allow only one NULL in SQL Server?

In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should allow any number of NULLs?

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL.

It wasn't a very smart decision. What they should have done is have the default behavior adhere to the ANSI standard, and if they really wanted this peculiar behavior, allow it through a DDL option like WITH CONSIDER_NULLS_EQUAL or WITH ALLOW_ONLY_ONE_NULL.

Of course, hindsight is 20/20.

And we have a workaround, now, anyway, even if it isn't the cleanest or most intuitive.

You can get the proper ANSI behavior in SQL Server 2008 and above by creating a unique, filtered index.

CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;

This allows more than one NULL value because those rows are completely left out of the duplicate checking. As an added bonus, this would end up being a smaller index than one that consisted of the entire table if multiple NULLs were allowed (especially when it isn't the only column in the index, it has INCLUDE columns, etc). However, you may want to be aware of some of the other limitations of filtered indexes:

• How filtered indexes could be a more powerful feature

StackExchange Database Administrators Q#80514, answer score: 66