Filtered Indexes Part 1: Nullable Columns

SQL Server 2008 introduced the concept of filtered indexes, whereby the index only indexes a portion of the data based on a WHERE clause in the filter definition. 
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(
    MyTableId int IDENTITY(1,1) NOT NULL
    ,UniqueCode nvarchar(10) NULL
)

If we want to create a unique constraint on UniqueCode but retain the ability to have nulls you’d create an index as follows:

CREATE UNIQUE NONCLUSTERED INDEX IX_tblMyTable_UniqueCode
    ON tblMyTable (
        UniqueCode ASC
    )
    WHERE UniqueCode IS NOT NULL

By 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.