Microsoft® SQL Server® 2008 Internals

Index Selection

Index selection is one of the most important aspects of query optimization. The basic idea behind index matching is to take predicates from a WHERE clause, join condition, or other limiting operation in a query and to convert that to an operation that can be performed against an index. Two basic operations can be performed against an index:

• Seek (for a single value or a range of values on the index key)

• Scan the index (forwards or backwards)

For Seek, the initial operation starts at the root of a B+ tree and navigates down the tree to a desired location in the index based on the index keys. Once completed, the query processor can iterate over all rows that match the predicate or until the last value in the range is found. Because ...