Microsoft® SQL Server 2008 R2 Unleashed

Optimizing with Filtered Indexes

SQL Server 2008 introduces the capability to define filtered indexes and statistics on a subset of rows rather than on the entire rowset in a table. This is done by specifying simple predicates in the index create statement to restrict the set of rows included in the index. Filtered statistics help solve a common problem in estimating the number of matching rows when the estimates become skewed due to a large number of duplicate values (or NULLs) in an index or due to data correlation between columns. Filtered indexes provide query optimization benefits when you frequently query specific subsets of your data rows.

If a filtered index exists on a table, the optimizer recognizes when a search predicate is compatible ...