Microsoft® SQL Server® 2012 Bible

1025

Chapter 45: Indexing Strategies

45

Query Path 5’s second query selected the StartDate column. Because StartDate isn’t

part of the IX_WorkOrder_ProductID index, SQL Server was forced to use a bookmark

lookup. To solve the problem, the following code adds StartDate to the IX_WorkOrder_

ProductID index so that the index can cover the query.

DROP INDEX Production.WorkOrder.IX_WorkOrder_ProductID

CREATE INDEX IX_WorkOrder_ProductID

ON Production.WorkOrder (ProductID)

INCLUDE (StartDate);

The include option (added in SQL Server 2005) adds the StartDate column to the leaf

level of the IX_WorkOrder_ProductID index — but not to the list of keys of the index.

This enables you to defi ne additional columns that cover queries without hitting the index

column or size limit. Included columns are stored at the leaf levels of the nonclustered

index, but not at the intermediate levels. The query optimizer can now solve the queries

with an index seek (as show in Figure 45-12):

SELECT WorkOrderID, StartDate

FROM Production.WorkOrder

WHERE ProductID = 757; -- 9 rows

SELECT WorkOrderID, StartDate

FROM Production.WorkOrder

WHERE ProductID = 945; –- 1,105 rows

As mentioned earlier, when a nonclustered index is defi ned on a clustered base table, the

clustered keys are stored in the nonclustered index to be used as a pointer back to

the base table. Because of this, the nonclustered index can satisfy queries that include

the clustered key columns. The following query fi lters by the nonclustered index key and

returns the clustered index key value:

SELECT WorkOrderID

FROM Production.WorkOrder

WHERE ProductID = 757;

The Ix_WorkOrder_ProductID nonclustered index has the ProductID column as the

key column, and the clustered index is defi ned on the WorkOrderID column which is avail-

able in the nonclustered index.

The next query is a rare example of a covering index. Compared to the previous query

path, this query adds the StartDate column in the where clause. Conventional wisdom

would say that this query requires an index scan because it fi lters by a nonkey column.

(StartDate is an included column in the index and not a key column.)

SELECT WorkOrderID

FROM Production.WorkOrder

WHERE ProductID = 945

AND StartDate = '2006-01-04';

c45.indd 1025c45.indd 1025 7/31/2012 10:16:41 AM7/31/2012 10:16:41 AM

http://www.it-ebooks.info