Sql Server Performance: Covering Index, A performance booster for query

In most cases, an index is used to quickly locate the data record(s) from which the required data is read. In other words, the index is only used to locate data records in the table and not to return data.

A covering index is a special case where the index itself contains the required data field(s) and can return the data. (wikipedia)

Let’s create a covering index and compare performance, especially when one or more columns are missing in covering index but on query side included in select clause.

USE [AdventureWorks]

GO

-- Drop if already exists

IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = 'ix_FirstName')

DROP INDEX [ix_FirstName] ON [Person].[Contact] WITH ( ONLINE = OFF )

GO

CREATE NONCLUSTERED INDEX [ix_FirstName] ON [Person].[Contact]

(

            [FirstName] ASC

)

--Included columns

INCLUDE ( 

[ContactID],

[NameStyle],

[Title],

[MiddleName],

[LastName],

[Suffix],

[EmailAddress],

[Phone],

[ModifiedDate])  ON [PRIMARY]

GO

This covering index contains values for following 9 columns

1.     [ContactID]

2.     [NameStyle]

3.     [Title]

4.     [MiddleName]

5.     [LastName]

6.     [Suffix]

7.     [EmailAddress]

8.     [Phone]

9.     [ModifiedDate]

Let’s check comparative performance of two queries, in first one all columns are satisfied from our above created index and other one contains one extra column in select clause.

USE AdventureWorks

SELECT    [ContactID],[NameStyle],[Title],[MiddleName],

[LastName],[Suffix],[EmailAddress],[Phone],[ModifiedDate]

FROM    Person.Contact

WHERE   FirstName LIKE N'Phil'

-- Query with one extra column [PasswordHash] which is not included in ix_FirstName index

SELECT    [ContactID],[NameStyle],[Title],[MiddleName],

[LastName],[Suffix],[EmailAddress],[Phone],[ModifiedDate],[PasswordHash]

FROM    Person.Contact

WHERE   FirstName = N'Phil'

Lesson Learned: Create covering indexes for best query performance and all

    columns must be satisfied by used covering index.