Microsoft® SQL Server® 2012 Bible

1017

Chapter 45: Indexing Strategies

45

Without a where clause and every column selected, the query must read every row from

the clustered index. A clustered index scan (shown in Figure 45-5) sequentially reads

every row.

FIGURE 45-5

The clustered index scan sequentially reads all the rows from the clustered index.

Clustered Index

PK_WorkOrder_WorkOrderID

This query is the longest query of all the query paths, so it might seem to be a slow query,

however, when comparing the number of rows returned per millisecond, the index scan

returns the highest number of rows per millisecond of any query path.

Query Path 2 — Clustered Index Seek

The second query path adds a where clause to the fi rst query and fi lters the result to a

single row using a clustered key value:

SELECT *

FROM Production.WorkOrder

WHERE WorkOrderID = 1234;

The query optimizer has two clues that there’s only one row that meets the where

clause criteria: Statistics and that WorkOrderID is the primary key constraint, so it

must be unique. WorkOrderID is also the clustered index key, so the query optimizer

knows there’s a great index available to locate a single row. The clustered index seek

operation navigates the clustered index B-tree and quickly locates the desired row, as

shown in Figure 45-6.

c45.indd 1017c45.indd 1017 7/31/2012 10:16:40 AM7/31/2012 10:16:40 AM

http://www.it-ebooks.info