Microsoft SQL Server 2012 Bible

Specialty Indexes

Beyond the standard clustered and nonclustered indexes, SQL Server offers two type of indexes referred to as specialty indexes. Filtered indexes, which were new in SQL Server 2008, include less data; and indexed views, available since SQL Server 2000, build out custom sets of data. Both are considered high-end performance tuning indexes.

Filtered Indexes

A nonclustered index contains a record for every record in the base table on which it is defined. Historically, this has always been a 1-to-1 relationship. SQL Server 2008 introduced the concept of a filtered index. With a filtered index, you can set a predicate on your CREATE INDEX statement so that the index contains only rows that meet the criteria you set. This option is only available for nonclustered indexes because a clustered index IS the table, so it wouldn't make sense to allow you to filter on a clustered index. Because a filtered index can potentially have much fewer records than the traditionally nonclustered index, they tend to be much smaller in size. In addition, because the index has fewer records, the statistics on these indexes tend to be more accurate, which can lead to better execution plans.

An example of employing a filtered index in AdventureWorks2012 is the ScrappedReasonID column in the Production.WorkOrder table. Fortunately, for AdventureWorks, they scrapped only 612 (.8 percent) parts over the life of the database. The existing IX_WorkOrder_ScrapReasonID includes every row. The ScrapReasonID ...