Microsoft SQL Server 2012 Performance Tuning Cookbook

Effect of statistics on a filtered index

The filtered column index is one of the enhancements for non-clustered indexes, provided in Microsoft SQL Server 2008, and it is 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 costs, reduces index storage, and improves query performance. Since this chapter is about statistics, we are not going to see how a filtered index works, in this recipe. Look at the Increasing performance by filtered index recipe in Chapter 9, Implementing Indexes, to learn more about filtered indexes.

Getting ready

There is a table named WorkOrder in the Production schema in database AdventureWorks2012, and this ...