Covering Indexes is a topic I like to return to every once in a while. As we get new readers with less SQL experience this can be a helpful technology to understand.
For any relational engine, a covering index is an index that contains all the data necessary to service an SQL statement. Let’s say you have a table with 40 columns. If you write a query that filters on two columns, and includes 5 columns in the select clause, then the database engine must determine how to find the filtered records, and then how to gather the remaining 5 columns of data.
If you have an index handling the 2 filtered columns alone, many engines will use that index to locate a pointer to where the whole record is stored. A covering index, unlike the previous index, contains all 7 columns. In this case, it searches on the index. However, since the remaining columns are contained in the index, it does not have to follow a pointer to the physical location of the base table data. It utilizes the data found within the index.
This method of using a covering index results in a lot of duplication of data, and overhead associated with maintaining the synchronization of the index with the base data as it changes over time. As a result, a covering index is not often used on an OLTP database. It is more often found in data mart, and data warehouse tables. You will find them in normalized databases as well, when they are used primarily for reporting rather than record capturing.
SQL Server has added the INCLUDES clause to their index, allowing you to define key columns, and then include additional columns in the index. This separation allows you to build covering indexes with lots of columns not related to the filtering requirements. This results in less work to maintain the index because it only has to sort the columns that are used for filtering. The additional column data simply come along for the ride, and are not applied to the constraints of an index key.
Do you have a report that is not performing as well as you would like? Perhaps you might try a covering index.
Cheers,
Ben