SQL Server : Covering Index

Covering Index term is used to describe a certain technique that is used to improve performance. It does not mean a separate kind of index having a different internal structure.

      A covering index is a form of a composite index, includes all of the columns referenced in the SELECT, JOIN and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn’t have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.

On the other hand, if the covering index gets too big (has too many columns), this could actually increase I/O and degrade performance. Generally, when creating covering indexes, follow these guidelines:

• If the query or queries you run using the covering index are seldom run, then the overhead of the covering index may outweigh the benefits it provides.
• The covering index should not add significantly to the size of the key. If it does, then it its use may outweigh the benefits it provides.
• The covering index must include all columns found in the SELECT list, the JOIN clause, and the WHERE clause.

To determine if a covering index could help a query’s performance is to create a graphical query execution plan in Query Analyzer or Management Studio and check for any Bookmark Lookups (RID or Key) being performed. Essentially, a Bookmark Lookup is indicate that the Query Processor had to look up the row columns it needs from a table or a clustered index, instead of being able to read it directly from a non-clustered index. Bookmark Lookups can reduce query performance because they produce extra disk I/O to retrieve the column data.One way to avoid a Bookmark Lookup is to create a covering index. This way, all the columns from the query are available directly from the non-clustered index, which means that Bookmark Lookups are unnecessary, which reduces disk I/O and helps to boost performance.

Example: (specifying the index explicitly in the query, forced SQL Server to use the non-clustered AK_Employee_NationalIDNumber index)

Query 2 is a covered query, a query where all the columns in the query's result set are pulled from non-clustered indexes.