1035
Chapter 45: Indexing Strategies
45
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 cus-
tom 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
defi ned. Historically, this has always been a 1-to-1 relationship. SQL Server 2008 introduced
the concept of a fi ltered index. With a fi ltered 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 fi lter on a clustered index. Because a fi ltered 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 fi ltered 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
foreign key in the Production.WorkOrder table enables nulls for work orders that were
not scrapped. The index includes all the null values with pointers to the workorder rows
with null ScrapReasonIDs. The current index uses 109 pages.The following script re-creates the index with a WHERE clause that excludes all the null values:DROP INDEX Production.WorkOrder.IX_WorkOrder_ScrapReasonIDCREATE INDEX IX_WorkOrder_ScrapReasonID
ON Production.WorkOrder(ScrapReasonID)
WHERE ScrapReasonID IS NOT NULL
The new index uses only two pages. Interestingly, the difference isn’t noticeable between
using the fi ltered or nonfi ltered index when selecting all the work orders with a scrap
reason that’s not null. This is because there aren’t enough intermediate levels to make a
signifi cant difference. For a much larger table, the difference would be worth testing, and
most likely the fi ltered index would provide a benefi t.Best Practice
When designing a covering index (see Query Path #6) to solve a specifi c query — probably one that is in
the top handful of CPU duration according to the indexing strategy — if the covering index works with a
relatively small subset of data, and the overall table is a large table, consider using a fi ltering covering index.c45.indd 1035c45.indd 1035 7/31/2012 10:16:44 AM7/31/2012 10:16:44 AM
http://www.it-ebooks.info