Microsoft SQL Server 2012 Bible

Indexing Basics

The following section presents an overview of the main indexes available in the SQL Server database engine. The rest of the chapter builds upon the ideas introduced in this section.

The B-Tree Index

The two main types of indexes in SQL Server are clustered and nonclustered indexes. Each index type is implemented via a balanced-tree (B-tree) data structure. A B-tree is a structure that stores data in a sorted order and enables fast access to the data it holds.

Although technically not the only type of index present in the database engine, this chapter focuses exclusively on the clustered and nonclustered indexes. Other index types, such as Spatial and XML indexes, are outside of the scope of this chapter.

B-tree indexes exist on index pages and have a root level, one or more intermediate levels, and a leaf level. When you define an index, you specify one or more key columns. These columns are actually sorted in the index, as defined 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 figure illustrates a simplified view of a clustered index with an identity column as the clustered index key. The first name is the data column.

Although this chapter discusses ...