Microsoft® SQL Server® 2012 Bible

1007

Chapter 45: Indexing Strategies

45

B-tree indexes exist on index pages and have a root level, one or more intermediate levels,

and a leaf level. When you defi ne an index, you specify one or more key columns. These

columns are actually sorted in the index, as defi ned in Figure 45-1. The difference between

clustered and nonclustered indexes is the way in which the data is stored at the leaf level

of the index.

FIGURE 45-1

This fi gure illustrates a simplifi ed view of a clustered index with an identity column as the

clustered index key. The fi rst name is the data column.

Key Columns

Balanced Tree Index Data Columns

1-3

4-6

7-9

10-12

1-6

7-12

1

2

3

Matt

Paul

Beth

4

5

6

Nick

Steve

Zack

7

8

9

Tom

Hank

Greg

10

11

12

Susan

Albert

Ingrid

Although this chapter discusses the strategies to design and optimize indexes and does include some

code examples that demonstrate creating indexes, the sister Chapter 7, “Relational Database Design

and Creating the Physical Database Schema,” details the actual syntax and Management Studio meth-

ods to create indexes.

Over time, indexes will likely become fragmented, which can potentially impact performance. For more information on

index maintenance, turn to Chapter 22 “Maintaining the Database.”

Clustered Indexes

When a clustered index is created on a table, the index itself becomes the table. This is

somewhat confusing at fi rst. When you create a clustered index, under the covers the

database engine sorts the data in the underlying table based on the index key(s) you

defi ne and stores the table in that order. The clustered index doesn’t become a separate

structure for the underlying table data like a nonclustered index does; the clustered index

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

http://www.it-ebooks.info