<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Covering Indexes - What are they?

Published on
8,932 Points
2,932 Views
Last Modified:
Approved
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
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

Open in new window

Execution plan using no indexes:

 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
0
Comment
Author:sqlxl
0 Comments

Featured Post

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month