In SQL Server 2008 a new feature was introduced – Filtered Indexes.  A filtered index is supposed to introduce performance gains by reducing the size of the index to discrete values. In order for a filtered index to be used, the query must not parameterize the value associated with the filtered index.  If you use non-parameterized SQL filtered indexes can help.

The following query uses a filtered index created where ProcessingStatus=’N’

SELECT COUNT(1) FROM CustomerAccount
WHERE ProcessingStatus = 'N'

The filtered index is named FIProcessingStatus.  This is filtered on where ProcessingStatus=’N’.  If we change the value to ‘Y’ we’ll see that the index doesn’t get used and we get a plan warning:

Similarly parameterized SQL has the same problem with filtered indexes:

Final thoughts

While filtered indexes can provide performance improvements, you should validate that they are being used by checking the sys.dm_db_index_usage_stats DMV.  I’ve seen many cases where there was a disconnect between what the DBA created and the developer coded