SQL Server: Optimizing Database Performance Through Indexes

• By Baya Dewald and Kevin Kline
• Aug 2, 2002
• Article is provided courtesy of Addison-Wesley Professional

Like this article? We recommend
Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

Appropriate indexes are a necessary part of any successful database application. In fact, you can see performance improvements of 10, 100, and sometimes even 1,000 fold by adding helpful indexes to your database tables. This article introduces the basics of clustered and non-clustered indexes, and gives you some ideas and tips for selecting a proper indexing strategy.

Indexes 101

Simply put, an index is a physical structure containing pointers to the data. SQL Server 2000 supports up to 250 indexes per table, although you'll generally have far fewer indexes on each table. You can't build indexes on columns with certain datatypes such as TEXT, NTEXT, and IMAGE.

There are two types of indexes: clustered and non-clustered. SQL Server index structure is often compared to the structure of a tree. Both clustered and non-clustered indexes have two types of nodes: leaf nodes and root nodes. The leaf level of the clustered index is the data itself. The leaf level of a non-clustered index is a pointer to the root level of the clustered index.

The clustered index key—that is, the column(s) on which the index is built—determines how data is physically ordered. So if you build a clustered index on state columns of the authors table in the Pubs database, the data will be ordered based on the values of state—in either ascending or descending order. The CREATE INDEX statement defaults to ascending order, but you can override it with the DESCENDING keyword if necessary.

After reading the previous paragraph, it shouldn't come as a surprise that you can have only one clustered index per table—you can store data ordered in only one way. Since the leaf level of the clustered index is the data itself, reading data using the clustered index is much faster than when using a non-clustered index. If you have a need for optimal performance, choose the column that has the clustered index very carefully.

Clustered indexes must be unique; since the data is ordered according to the values in the clustered index key, SQL Server must have a way to uniquely identify the position of each data row in the index. If you have duplicate values in the clustered key, SQL Server will add a hidden column (key) to the index to make it unique.

Non-clustered indexes are not as efficient as clustered indexes; however, scanning a non-clustered index can still be much more efficient than scanning an entire table. Since you can have only one clustered index per table, all other indexes you need must be non-clustered.