O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required