Covering Index or Index with Included Columns

Covering Index or Index with included Columns is a new functionality which is introduced in SQL Server 2005 onwards and is an extension to Non-Clustered Indexes.

From SQL Server 2005 onwards, the functionality of the Non-Clustered indexes can be extended by adding non-key columns to the leaf level of the Non-Clustered index. By including non-key columns, we can create Non-Clustered indexes that cover more queries.

This is because the non-key columns have the following benefits:

• They can be data types that are not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.

So creating Covering Indexes can significantly improve the query performance because all the columns in the query are included in the index itself, either as key or nonkey columns. And only the index pages and not the data pages will be used in retrieving the data.

Covering indexes can bring in a lot of performance to the query, because it can save a huge amount of I/O operations.

How to Create a Covering Index:

Using T-SQL:

/*This query will create a non-clustered index by name IX_Address_PostalCode

on PostalCode Column and includes the non-key columns AddressLine1,

AddressLine2, City, StateProvince */

USE AdventureWorks;

GO

CREATE NONCLUSTERED INDEX IX_Address_PostalCode

ON SalesLT.Address (PostalCode)

INCLUDE (AddressLine1, AddressLine2, City, StateProvince);

GO

/* And the below Query will be covered by the index and gives more performance */

SELECT AddressLine1, AddressLine2, City, StateProvince, PostalCode

FROM SalesLT.Address

WHERE PostalCode BETWEEN '85000' and '90000';

GO

Using GUI:

This article is also available in pdf format for downloading.

Please Click here to get your copy.