Microsoft® SQL Server® 2012 Bible

1009

Chapter 45: Indexing Strategies

45

Nonclustered Indexes

SQL Server nonclustered indexes are also implemented as a B-tree data structure. The dif-

ference between a clustered index and a nonclustered index is that the leaf level pages

of the nonclustered index do not contain all the base table data like the clustered index

does. Instead, the leaf level of the nonclustered index contains the index keys along with

a pointer to the base table. If the nonclustered index is not unique, all levels of the index

contain a pointer to the base table. If the base table is a clustered index, the clustered keys

are stored in the nonclustered index. If the base table is a heap, the nonclustered index

contains the row-identifi er for the base table record.

For example, the nonclustered index shown in Figure 45-2 uses the fi rst name column as

its key column so that’s the data sorted by the index. The nonclustered index points to the

base table by including the clustered index key column. In Figure 45-2, the clustered index

key column is the identity column used in Figure 45-1.

FIGURE 45-2

This simplifi ed illustration of a nonclustered index has a B-tree index with a fi rst name as the

key column. The nonclustered index includes pointers to the clustered index key column.

Key Columns

Nonclustered Index Clustered Keys or Heap RowID

A-G

H-M

N-st

Su-Z

A-M

N-Z

11

3

9

8

12

1

4

2

5

10

7

6

Albert

Beth

Greg

Hank

Ingrid

Matt

Nick

Paul

Steve

Susan

Tom

Zack

This is an important fact to consider when designing your nonclustered indexes. If your

clustered key is large, such as a uniqueidentifi er, that large data type will be included in

the nonclustered index key for each row. So, a large clustered key can directly affect the

size of your nonclustered indexes.

c45.indd 1009c45.indd 1009 7/31/2012 10:16:38 AM7/31/2012 10:16:38 AM

http://www.it-ebooks.info