What's a good use for a UNIQUE filtered index?

Question: I get the idea of filtered indexes but I can’t see a good use to a UNIQUE filtered index? When would that be beneficial?

Answer: Actually, I’ve got a fairly straightforward and simple example that might help solve what used to be a common frustration around allowing multiple NULL values in a column where you want to enforce uniqueness (over the non-NULL values). However, I’ll expose a bit of a [minor] limitation if you use it.

Imagine having a Employees table where you’ve chosen to create a contrived (identity) column as the EmployeeID. You also want to have an alternate key for the SSN. However, you’d like to allow NULLs in the SSN column. If you create a unique constraint or a unique index on the SSN column, you can insert only one row that has a NULL value for SSN. So, this probably isn’t what you want!

Prior to SQL Server 2008 the solution was to create an indexed view to handle this requirement. But, with filtered indexes it becomes even easier:

CREATE UNIQUE INDEX EmployeeSSN_UIndON Employee (SSN)WHERE SSN IS NOT NULL

However, there’s a catch. If you need to reference this column from another table – you can’t. SQL Server allows a foreign key to reference any primary key, unique key or unique index – as long as it’s not filtered. This is a bummer IMO.

If you don’t need to reference the column then a unique filtered index can be a nice way to handle enforcing uniqueness over your non-NULL values! But, if you need a foreign key then this option won’t work for you.

Finally, don’t forget that filtered indexes have the same rules around session settings as indexed views have. This is important to know because applications that connect to SQL Server without the correct session settings will actually fail on INSERT, DELETE or UPDATE. And, queries that don’t have the correct session settings won’t be able to leverage the filtered index (for performance). For more information see the books online topic: Set Options that Affect Results.

Thanks for reading and have a great Thanksgiving holiday!

Kimberly

Note: A new blogger (that mailed me about their idea for unique filtered indexes) has just posted an interesting use of a unique filtered index. So, another interesting use to maintain that only one row (for a given product) is active at any time. Enjoy!