Database Reference
In-Depth Information
Figure 2-21.
Selecting data for five prefixes: execution plans
As you see, in our case, selecting 12,500 rows using a
Nonclustered Index Seek
introduced more logical reads when
compared to scanning the entire table. It is worth mentioning that 12,500 rows are less than 1 percent of the total
number of rows in the table.
This threshold varies, although it is very low
. We will discuss how SQL Server performs
such an estimation in the next chapter.
■
sQl server does not use nonclustered indexes if it estimates that a large number of
Key Lookup
operations will be required.
Important
Nonclustered indexes help improve the performance of queries although this comes at its own price. They
maintain the copy of the data from the index columns. When columns are updated, SQL Server needs to update them
in the every index they are included.
Even though SQL Server allows creating either 250 or 999 nonclustered indexes per table, depending on
the version, it is not a good idea to create a lot of them. We will talk about indexing strategies in Chapter 6,
“Designing and Tuning the Indexes.”
Summary
Clustered indexes define the sorting order for data in a table. Nonclustered indexes store a copy of the data for a
subset of table columns sorted in the order in which the key columns are defined.
Both clustered and nonclustered indexes are stored in a multiple level tree-like structure called
B-Tree
.
Data pages on each level are linked in a double-linked list.
