Browse All Articles > Covering Indexes - What are they?
Covering Indexes Definition
An index that contains all information required to resolve a query without additional ‘lookups’.
Explanation
Covering indexes are the same as basic non-clustered database index, except for the fact that they actually contain the required data and can return it.
In most queries, the SQL engine finds the necessary rows in the index and then goes to the underlying table to get the data. If the index also contains the data fields, as is the case with a covering index, then the SQL engine doesn’t need to go to the underlying table for the data, it gets it all from the index itself. This is a technique used to improve performance.
These indexes are based off of a key column, but then are also able to include non-key columns needed by the query. By creating an index that encapsulates all columns of a query, you are essentially creating a much smaller dataset than the table itself.
During the execution of the query, the engine can now focus all its resources on the smaller index to find the associated rows without even touching the actual database table(s) listed in the query. This then requires much less logical and/or physical disk I/O and conversely reduces the time taken to retrieve the rows.
Benefits
Drawbacks
As there is with any performance tuning technique, there are some disadvantages to be aware of.
Example
Using a basic filtered query, we can see the execution plan with and without indexes in play.
http://sqlxl.com/images/covering_index_image001.png
Logical Reads: 2569
Execution plan using a covering index on ContactID (key), FirstName (included), LastName (included):
http://sqlxl.com/images/covering_index_image002.png
Logical Reads: 566
By implementing a basic covering query, we just reduced the logical reads by over 450%. The database engine will no longer need to perform a full table scan, and can rely solely on the index to perform the scan.
This is Article originally appears on our website: http://sqlxl.com/covering_index.php
An index that contains all information required to resolve a query without additional ‘lookups’.
Explanation
Covering indexes are the same as basic non-clustered database index, except for the fact that they actually contain the required data and can return it.
In most queries, the SQL engine finds the necessary rows in the index and then goes to the underlying table to get the data. If the index also contains the data fields, as is the case with a covering index, then the SQL engine doesn’t need to go to the underlying table for the data, it gets it all from the index itself. This is a technique used to improve performance.
These indexes are based off of a key column, but then are also able to include non-key columns needed by the query. By creating an index that encapsulates all columns of a query, you are essentially creating a much smaller dataset than the table itself.
During the execution of the query, the engine can now focus all its resources on the smaller index to find the associated rows without even touching the actual database table(s) listed in the query. This then requires much less logical and/or physical disk I/O and conversely reduces the time taken to retrieve the rows.
Benefits
Typically and index in restricted to 16 key columns. By including non-key columns to your index, you can now have up to 1023 included columns
The SQL engine does not need to go to the underlying table(s) for data rows.
Drawbacks
As there is with any performance tuning technique, there are some disadvantages to be aware of.
Having many non-clustered indexes can slow down the Insert/Update/Delete actions on that table
Datatypes text, ntext and image cannot be used as non-key columns within the index
Non-key columns cannot be dropped from the table unless the index is dropped first
Disk space is required to store the index itself. Information will now reside in both the index and table
Example
Using a basic filtered query, we can see the execution plan with and without indexes in play.
SELECT ContactID,
FirstName,
LastName
FROM ContactMaster where LastName > 'Coates' order by FirstName
http://sqlxl.com/images/covering_index_image001.png
Logical Reads: 2569
Execution plan using a covering index on ContactID (key), FirstName (included), LastName (included):
http://sqlxl.com/images/covering_index_image002.png
Logical Reads: 566
By implementing a basic covering query, we just reduced the logical reads by over 450%. The database engine will no longer need to perform a full table scan, and can rely solely on the index to perform the scan.
This is Article originally appears on our website: http://sqlxl.com/covering_index.php
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)