Covering Indices

06 Nov 2010

SQL


If you create an index containing all the columns (or more) that you need for a query, then this is referred to as a covering index. SQL Server will return information it finds within a covering index, but only if you specify the columns you want directly within a query. Using the asterisk operator forces SQL Server to go to the table to retrieve the data, potentially slowing data access down.

Consider the following:

SELECT * FROM tbTable WHERE (etc)

Here, SQL Server will use appropriate indices to assist in row resolution based on the WHERE clause in order to speed data retrieval, but will then go to the table to retrieve the data.

SELECT Column1, Column2 FROM tbTable WHERE (etc)

Here, SQL Server will return the data *directly* from the underlying covering index *without* having to go to the table to retrieve the data. As you could imagine, this method of data retrieval can be many times faster, and is one of the benefits of specifying the columns you require explicitly in your SELECT statement.


 

Copyright © 2024 carlbelle.com