Microsoft® SQL Server® 2012 Bible

1029

Chapter 45: Indexing Strategies

45

For infrequent queries, Query Path 7, with its multiple indexes, is more than adequate and

much better than no index at all. However, for those few queries that run constantly, the

next query path is a better solution for multiple criteria.

Query Path 8 — Filter by Ordered Composite Index

For the raw performance, the fastest solution to the multiple-where-clause-criteria problem

is a single composite index as demonstrated in Query Path 8.

Creating a composite index with ProductID and StartDate as key columns sets up the

test:

DROP INDEX Production.WorkOrder.IX_WorkOrder_ProductID

DROP INDEX Production.WorkOrder.IX_WorkOrder_StartDate

CREATE INDEX IX_WorkOrder_ProductID

ON Production.WorkOrder (ProductID, StartDate);

Rerunning the same query,

SELECT WorkOrderID, StartDate

FROM Production.WorkOrder

WHERE ProductID = 757

AND StartDate = '2006-01-04';

The query execution plan, as shown in Figure 45-15, is a simple single index seek operation,

and it performs wonderfully.

FIGURE 45-15

Filtering two criteria using a composite index performs like greased lighting.

c45.indd 1029c45.indd 1029 7/31/2012 10:16:42 AM7/31/2012 10:16:42 AM

http://www.it-ebooks.info