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_UInd
ON 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!

Discuss this Blog Entry 3

on Nov 18, 2011
With the presence of unique filtered index, if my query criteriion is something like "where ssn is null". Why sql server could not take advantage of the unique filtered index by except the these rows followed by the table scan or clusted index scan? Thanks, Pei Zhu
on Nov 17, 2011
I've used them in a manner similar to Rafi, where a given entity has multiple attributes, only one of which can be active (IsActive = 1) at a time. In the past we used a trigger to enforce this, now I do it with a filtered index. Cleaner, with the added benefit of potentially improving performance.
on Nov 21, 2011
I use it to enforce data integrity in our data warehouse. Currently there are two business systems feeding the DW with data, and some tables (like Orders.SalesDetail) containts data from both systems. However, since the two systems have unique keys over a different set of columns, I use unique filtered index to maintain data integrity in the data warehouse for ETL processes and added an identity column as nonclustered primary key and put the ChangedDateTime and Identity column together as a clustered index.

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×