Chapter 6. Optimizing Access to Data: Indexes

Perhaps the main reason to install a database system is to be able to search efficiently for data. Commercial systems use vast amounts of data, and users have come to expect a reasonably short response time when searching for information irrespective of how the search is carried out or the criteria used in the search.

Many other programming books and technical papers have already covered searching and sorting algorithms, so it is not the purpose of this book to introduce any new theories on this subject.

To produce results in the quickest and most efficient way, SQL Server must have fast access to the data. It does this by allowing every operation to have optimized access to any resource that it might need to use.

This chapter teaches you

• How to use indexes in everyday operations

• How SQL Server 2000 implements indexes

• How SQL Server 2000 accesses data from tables

• The differences between clustered and nonclustered indexes

• How to create, modify, and delete indexes

• How SQL Server 2000 accesses data stored as a clustered index

• How SQL Server 2000 accesses data stored as a heap

• How to create an index to cover a query

• What index fragmentation is and how to manage it

• How to use the Index Tuning Wizard