1013
Chapter 45: Indexing Strategies
45
clustered key if the base table is clustered. The clustered keys are used in the nonclustered
index to navigate back to the clustered index; they’re basically used as a pointer in the
nonclustered index, so the base table can be used if additional columns are needed for a
given query. If the base table is not clustered, then any nonclustered index can store the
heap’s row identifi er in every level of the index, which is used to point back to the full row
in the base table.In a sense, you can think of SQL Server having two different types of tables: a clustered
(index) table and a heap table, which are mutually exclusive. A table can never be a heap
and a clustered table at the same time.Query Operators
Although there are dozens of logical and physical query execution operations, SQL Server
uses three primary operators to access data. These are also known as access methods.■ (^) Table Scan: Reads the entire heap and, most likely, passes all the data to a second-
ary fi lter operation.
■ (^) Index Scan: Reads the entire leaf level (every row) of the clustered index or non-
clustered index. The index scan operation might fi lter the rows and return only
those rows that meet the criteria, or it might pass all the rows to another fi lter
operation depending on the complexity of the criteria. The data may or may not be
ordered.
■ (^) Index Seek: Locates specifi c row(s) of data using the B-tree and returns only the
selected rows in an ordered list (see Figure 45-4).
FIGURE 45-4
An index seek operation navigates the B-tree index, selects a beginning row, and then scans
all the required rows.
Clustered Index Seek
Seek
Scan
c45.indd 1013c45.indd 1013 7/31/2012 10:16:39 AM7/31/2012 10:16:39 AM
http://www.it-ebooks.info