SQL Server UNIQUE constraint with duplicate NULLs
indexing, sql-server
Solution
If you're using SQL Server 2008 (won't work for earlier version) there is the concept of a filtered index. You can create the index on a filtered subset of the table.
CREATE UNIQUE INDEX indexName ON tableName(columns) INCLUDE includeColumns
WHERE columnName IS NOT NULL
Problem
Possible Duplicate: How do I create unique constraint that also allows nulls in sql server I have a table where I need to force a column to have unique values. This column must be nullable and by business logic multiple NULL values should be permitted, whereas other duplicate values are not. SQL Server UNIQUE constraint is no good in this situation because it considers NULL as regular values, so it will reject duplicate NULLs. Currently, value uniqueness is granted by the BLL so I'm not looking for a dirty hack to make it work. I just would like to know if there is a clean solution to enforce this constraint in the DB. And yeah, I know I can write a trigger to do that: is a trigger the only solution? (or the best solution anyway?)