<

Covering Indexes - What are they?

Published on
8,879 Points
2,879 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month