Covering Indexes

A special situation that occurs with nonclustered indexes is called the covering index. A covering index is a nonclustered index built upon all of the columns required to satisfy an SQL query, both in the selection criteria and in the WHERE clause. Covering indexes can save I/O and improve query performance. But you must balance the costs of creating a new index (with its associated B-tree index structure maintenance) with the I/O performance gain the covering index will bring. If a covering index will benefit a query or a set of queries that run often on SQL Server, creating the covering index may be worthwhile, for example:

SELECT col1,col3 FROM table1 WHERE col2 = 'value'

    CREATE INDEX indexname1 ON table1(col2,col1,col3)

Or from SQL Server Enterprise Manager, use the Create Index Wizard.

The indexname1 index in the preceding example is a covering index because it includes all columns from the SELECT statement and the WHERE clause. During the execution of this query, SQL Server does not need to access the data pages associated with table1. SQL Server can obtain all of the information required to satisfy the query by using the index called indexname1. When SQL Server has traversed the B-tree associated with indexname1 and has found the range of index keys where col2 is equal to value, SQL Server fetches all required data (col1,col2,col3) from the leaf level of the covering index. This improves I/O performance in two ways:

• SQL Server obtains all required data from an index page, not a data page; therefore, the data is more compressed and SQL Server saves disk I/O operations.
• The covering index organizes all of the required data by col2 physically on the disk. The hard disk drives return all of the index rows associated with the WHERE clause (col2 = value) in sequential order, which gives better I/O performance. From a disk I/O standpoint, a covering index becomes a clustered index for this query and any other query that can be satisfied completely by the columns in the covering index.

If the number of bytes from all the columns in the index is small compared to the number of bytes in a single row of that table, and you are certain the query taking advantage of the covered index will be executed frequently, then it makes sense to use a covering index. But, before building many covered indexes, consider how SQL Server 7.0 can effectively and automatically create covered indexes for queries on the fly.