Month: October 2012

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