Improve performance using filtered indexes

A filtered index is a non-clustered index that contains only a subset of the number of rows contained in a table. You add a WHERE clause to reduce the number of rows that are stored at the leaf level.

CREATE NONCLUSTERED INDEX <Index_Name>
ON <Table Name>(<Column(s)>)
WHERE <Filter Condition>

A good real example it is use is on Status column, as you might have a large number of rows with Closed status that are not part of your current searches (the business wants to query for Open, Processing, Invoicing, etc)

CREATE NONCLUSTERED INDEX [IX_Orders_IdStatus_Filterered]
ON [dbo].[Orders] ([IdStatus])
WHERE ([IdStatus] IN ('O', 'P', 'I'))

Another well-suited scenarios is where you frequently have to filter out NULL values.

Pros:

  • By having fewer rows in an index, less I/O is done when that index is used.
  • Index size is dramatically reduced.
  • The statistics on these filtered indexes are filtered as well, which typically results in them being more accurate.
  • Cons:

  • Use certain expressions, such as BETWEEN, NOT IN, or a CASE statement.
  • Parameterized queries doesn't take advantage of filtered indexes.
  • Is this useful?