INDEXING CONSIDERATIONS - A Developer's Guide to Data Modeling for SQL Server - page 227

this business. We talk about that in a minute, but first we need to discuss
the other major type of index.

Nonclustered Indexes
A nonclustered index is one that simply stores pointers to the pages that
contain the rows of data you are looking for. If table is a heap—that is, if it
has no clustered index—then these pointers include the page number and
the row identifier for the rows that contain the key value being searched
for. If the table has a clustered index, then the nonclustered index has a
pointer to the clustered index key for the rows. To put it in a slightly sim-
pler way, a nonclustered index points to the row (for a heap) or to the clus-
tered index key.
One way to remember the difference between a clustered index and a
nonclustered index is that a clustered index includes the data pages, and
a nonclustered index only points to the data pages. In our topic example, a
nonclustered index is like the topic's index; you find what you are looking
for in the index, and it points you to the information somewhere in the
book. If the topic were a clustered index, all the information in the topic
would be reordered, as with a dictionary. Remember that a clustered index
actually orders the rows on disk according to the key value. A nonclustered
index doesn't manage the data; it simply tells you where the data happens
to be.
A nonclustered index can be very useful when your data is already or-
dered—because of either a business rule or a clustered index—but the
query being issued doesn't necessarily require the ordered data value. Take
a look at Figure 10.5, which shows a simple B-tree, and we'll talk about
when you would use a nonclustered index.

IAM
Page

Index Rows

Link List Link List Link List

Pointer Rows
A-J

Pointer Rows
K-R

Pointer Rows
S-Z

F IGURE 10.5

Representation of a nonclustered index B-tree