Filtered Indexes Part 2: More With Unique Constraints

In my last post I showed you how you could enforce a unique constraint on a nullable field using SQL 2008’s filtered indexes.  In a recent project I needed to have a unique code in a lookup table.  However, the code was only required to be unique for active records.  In other words, two records could have the code “ABC” as long as only one of the records was active.
Imagine the following table:

CREATE TABLE tblMyTable(
    MyTableId int IDENTITY(1,1) NOT NULL,
    UniqueCode nvarchar(10) NOT NULL,
    IsActive bit NOT NULL DEFAULT 1
)

To enforce a unique constraint on the UniqueCode filed only when the IsActive field is true you would create the following index:

CREATE UNIQUE NONCLUSTERED INDEX IX_tblMyTable_UniqueCode
    ON tblMyTable (
        UniqueCode ASC
    )
    WHERE IsActive = 1

In this case the WHERE causing the index to only include those UniqueCode values where the IsActive field is 1 and thus only checking for uniqueness on active records.