Tuesday, February 1, 2011

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

No comments:

Post a Comment