Optimizing with Filtered Indexes
SQL Server 2012 provides the ability 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 NULL
s) 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 ...
Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.