Articles → SQL SERVER → Covering index in SQL server

Covering index in SQL server

In this article I will explain about covering indexes in SQL server. But before I explain this concept, let us discuss about some basic concepts.

Technical knowledge

Before you start reading this tutorial you should make sure that you know

   1. Basics of SQL server

   2. Clustered and non-clustered index

   3. What is the difference between scan and seek?

   4. Basics about execution plans.

Non-clustered index and key loop ups

Consider an example of Task table with the following schema

Column Name	Data Type
Id	int
Title	varchar(300)
Deadline	Date
Assigned To	varchar(100)

In the above table clustered index is created on ‘Id’ column and non-clustered index is created on ‘Title’ column.

In case of non-clustered index all index columns are stored in different data page. So logically we have 2 tables i.e. actual and non-clustered index table.

Click to Enlarge

So if we submit a select query to SQL server and select query contains only those columns on which an index is created i.e. Title, then non-clustered index table returns the result without going to the actual table.

select Title from Task where Title = 'Task 1'

You can check it through execution plan.

Click to Enlarge

But if the select query contains those columns which do not contain indexes (those columns are called non-key columns) then non-clustered index table will go to the actual table and get that information. This is called key loop up.

select Title, Deadline from Task where Title = 'Task 1'

So the execution plan will be

Click to Enlarge

Covering index

SQL server provides an option in which we can include non-key columns while creating a non-clustered index. These non-key columns are those we use in select statement or where clause.

Syntax –

CREATE NONCLUSTERED INDEX non_clustered_index_name ON table_name(column_name) include (list_of_included_columns)

In the example above we can remove the existing non-clustered index and again create it with included columns

CREATE NONCLUSTERED INDEX ix_Title ON Task(Title) include (Deadline)

Now if you run the query again you could see from the execution plan that non-clustered index is not going to the actual table for getting the information.

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Friday, April 03, 2015

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250