Chapter 17 -- Creating and Using Indexes

3 4

Chapter 17

Indexes are one of the most powerful tools available to the database designer. An index is an auxiliary structure that enables you to improve the performance of queries by reducing the amount of I/O activity necessary to retrieve requested data—that is, an index enables Microsoft SQL Server 2000 to locate data by using fewer I/O operations than it would need to look up the data by accessing the database table alone. When you use a database table index to search for a row of data, SQL Server can quickly determine where the data is stored and immediately retrieve that data. Thus, database table indexes are much like indexes in books—they both provide fast access to large amounts of information.

In this chapter, you'll learn the basics of indexing, including how to create an index and the types of indexes that are available with SQL Server. You'll also learn when to use indexes and when not to use them because using an index is not always effective—in some situations, it can actually hurt performance.