Tuesday, May 13, 2014

SQL 2008 Create a unique index on field with NULL values

According to ANSI standards a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values. In SQL Server a single NULL is allowed but multiple NULL values are not.

In SQL Server 2008,  it is possible to define a unique filtered index based on a predicate that excludes NULLS:

CREATE UNIQUE NONCLUSTERED INDEX ix_Mycolumn_notnull
ON MyTable(Mycolumn)
WHERE Mycolumn IS NOT NULL;

No comments: