I’ve toyed with some potential uses of this, such as keeping archived data in its original table (an entry for another day). However, recently a more immediate use came up for it. Solving the age old problem of placing a unique constraint on a nullable field.
An index treats a NULL like any other value and so a unique index on a nullable field only allows a single record with a NULL value. In prior versions of SQL Server you would need to create triggers to enforce a unique constraint. However, in SQL 2008, by filtering the index to exclude NULLs, you can create a unique constraint on nullable.
Imagine the following table:
CREATE TABLE tblMyTable(If we want to create a unique constraint on UniqueCode but retain the ability to have nulls you’d create an index as follows:
MyTableId int IDENTITY(1,1) NOT NULL
,UniqueCode nvarchar(10) NULL
CREATE UNIQUE NONCLUSTERED INDEX IX_tblMyTable_UniqueCodeBy using the WHERE clause in the CREATE INDEX script you are removing NULLs from the index and thus ensuring that only non-null values are unique.
ON tblMyTable (
WHERE UniqueCode IS NOT NULL