Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

In this chapter, I showed you how SQL Server indexes organize the data on disk and help you access your data more quickly than if no indexes existed. Indexes are organized as B-trees, which means you will always traverse through the same number of index levels when you traverse from the root to any leaf page. To use an index to find a single row of data, SQL Server never has to read more pages than there are levels in an appropriate index.

You also learned about all the options available when you create an index, how to determine the amount of space an index takes up, and how to predict the size of an index that doesn't have any data yet.

We looked at the internal structure of various kinds of index structures, and we looked at the metadata for tables and indexes on a single partition and on multiple partitions. I described what happens to these structures as data modifications operations are carried out.

Indexes can become fragmented as many data modifications are applied. When you want to defragment your indexes, you have several methods to choose from, some of which allow the index to continue to be used by other operations while the defragmentation operation is going on.