Database Reference
In-Depth Information
The leaf level of the clustered index stores the actual table data. The intermediate and root level pages store one
row per page from the previous level. Every row includes the physical address and minimum value of the key from the
page that it references.
The leaf level of a nonclustered index stores the data from the index columns and row-id. For tables with a
clustered index, row-id is the clustered key value of the row. Intermediate and root levels of a nonclustered index
are similar to a clustered index, although when the index is not unique, those rows store row-id in addition to the
minimum index key value. It is beneficial to define indexes as unique because it makes the intermediate and root
levels more compact. Moreover, uniqueness helps Query Optimizer generate more efficient execution plans.
SQL Server needs to traverse the clustered index tree to obtain the data from the columns that are not part of the
nonclustered index. Those operations, called
Key Lookups
, are expensive in terms of I/O. SQL Server does not use
nonclustered indexes if it expects that large number of
Key Lookup
operations will be required.
Tables with a clustered index usually outperform heap tables. It is thus beneficial to define a clustered index on
tables in most of the cases.
SQL Server can utilize indexes in two separate ways. The first way is an
Index Scan
operation, where it reads
every page from the index. The second one is an
Index Seek
operation, where SQL Server processes just a subset of
the index pages. It is beneficial to use SARGable predicates in queries, which allows SQL Server to perform
Index Seek
operations by exactly matching the row or range of rows in the index.
You should avoid calculations and/or function calls against data columns because it makes predicates
non-SARGable. You should also take care to use correct data types for the parameters, especially when dealing
with unicode and non-unicode strings.
