Professional SQL Server® 2005 Performance Tuning

11.2. Section 1: Indexing Review

Indexes are used to provide fast access to data and are very often the first area to look at when tuning a system. This is because the performance gains for well-designed and maintained indexes can be considerable and they can be implemented relatively quickly. Adding, removing, or rebuilding an index can be a quick win for you in terms of performance so it is definitely worth your time to learn more about what they are and what indexing strategies SQL Server professionals employ.

There are two basic types of indexes: clustered and nonclustered. A clustered index defines the physical order that the data in a table is stored. You can only have one per table and it can be compared to the contents page at the beginning of this book. For example, to get to this chapter you might have read the contents page to get the page number and come straight here, or you might just have flicked through all the pages until you got here. In SQL Server terms these relate to a clustered-index seek and a clustered-index scan, respectively.

A non-clustered index does not affect the physical ordering of the data and can be compared to the index at the back of this book. If you wanted to know which page discussed "Fill Factor," for example, you could look at the index, get the exact page number, and go straight there. You can have up to 249 non-clustered indexes on single table but it's difficult to imagine a scenario where that would be a better choice than normalizing ...