Microsoft SQL Server 2012 Performance Tuning Cookbook

Improving performance by a filtered index

The filtered column index is one of the enhancements of the non-clustered index provided in Microsoft SQL Server 2008, and it is still available in SQL Server 2012. We can consider a simple non-clustered index with the WHERE clause, as a filtered index. A well-defined filtered index reduces maintenance cost and index storage, and improves query performance.

Actually, a non-clustered index, whether it is a covering or include index, indexes all the rows available in a table, whereas a filtered index indexes only those rows that meet the criteria given in the WHERE clause of the CREATE INDEX command. This is the main reason that a filtered index needs low storage and displays improved performance.

Getting ...