Summary

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 that 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.

Indexes can become fragmented in SQL Server 2000, but the performance penalty for fragmentation is usually much less than in earlier versions of SQL Server. When you want to defragment your indexes, you have several methods to choose from, one of which allows the index to continue to be used by other operations, even while the defragmentation operation is going on.

Finally, you learned about some of the situations in which you can get the greatest performance gains by having the appropriate indexes on your tables.