Unique constraints in Sql Server do not allow multiple NULL values

For some obscure reason Sql Server does not allow multiple NULL values on columns with UNIQUE constraints. This is in conflict with ANSI standards and also very contra-intuitive.

If you think this is poorly designed, you’re not alone: http://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values

However, there is a way to create a constraint that allows multiple NULL values rather easily (this uses “filtered constraints”, introduced in Sql Server 2008):

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

Credits: http://stackoverflow.com/questions/767657/how-do-i-create-unique-constraint-that-also-allows-nulls-in-sql-server

/Emil