Database Reference
In-Depth Information
Nonclustered Indexes
While a clustered index specifies how data rows are sorted in a table, nonclustered indexes define a separate sorting
order for a column or set of columns and persist them as a separate index structure.
You can think about a book as an example. Page numbers would represent the topic's
clustered index
. The
Index at the end of the topic shows the list of terms from the topic in alphabetic order. Each term references the page
numbers where the term is mentioned. That represents the
nonclustered index
of the terms.
When you need to find a term in the topic, you can look it up in the index. It is a fast and efficient operation
because terms are sorted in alphabetic order. Next, you can quickly find the pages on which the terms are mentioned
using the page numbers specified there. Without the index, the only choice would be reading all of the pages in the
book one by one until all references to the term are found.
The nonclustered index structure is very similar to the clustered index structure. Let's create a nonclustered index
on the
Name
column from the
dbo.Customers
table, as shown in Listing 2-12. Figure
2-17
shows the structure of both
indexes.
Listing 2-12.
Creating nonclustered index
create nonclustered index IDX_NCI on dbo.Customers(Name)
Figure 2-17.
Clustered and nonclustered index structures
The leaf level of the nonclustered index is sorted based on the value of the index key—
Name
in our case. Every
row on the leaf level includes the key value and
row-id
. For heap tables,
row-id
is the physical location of the row
defined as
file:page:slot
address.
■
another reason why sQl server uses forwarding pointers in heap tables is to prevent the updating of non-
clustered index rows when the original row in the heap table has been moved to another data page after the update.
nonclustered indexes keep the old
row-id
, which references the forwarding pointer row.
Note
For tables with a clustered index,
row-id
represents the value of the clustered index key of the row.
