SQLSERVER: Column with distinct value but mulitple NULL value?

Question

I have a requirement in which a column in the table should have distinct values but it may have multiple null values. How can I achieve that?

Answer 1

In SQL Server 2008, you can define a unique filtered index based on a predicate that excludes NULLs:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;
In earlier versions, you can resort to VIEWS with a NOT NULL predicate to enforce the constraint.