Articles → SQL SERVER → Covering Index In SQL Server

Covering Index In SQL Server

In this article, we will discuss covering indexes in the SQL server.

Technical Knowledge

Before you start reading this tutorial, you should make sure that you are familiar with the following concepts: -

Basics of SQL server
Clustered and non-clustered index
What is the difference between scan and seek?
Basics about execution plans

Non-Clustered Index And Key Loop Ups

Consider an example of the "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, a clustered index is created on the "Id" column, and the non-clustered index is created on the "Title" column.

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

Picture showing the logical representation of table having non-clustered index applied on the columns

So, if we submit a select query to the SQL server and the select query contains only those columns on which an index is created i.e., Title, then the 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 the execution plan.

Picture showing the execution plan of the select query having columns where non-clustered index is applied

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

Picture showing the execution plan of the select query having columns where non-clustered index is not applied

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.

Picture showing the select query with the column where covering index is applied

Posted By -	Karan Gupta

Posted On -	Friday, April 3, 2015

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**