SQL Server Covering Index 1

Hi Friends,

Today, here I just want to explain ‘what is SQL Server Covering Index?’ as well as ‘why we use Covering Indexes?’

What is Covering Index?

“Covering Index is a different case of index, where index contain all the data columns required to serve a particular query.”

Why we use covering indexes?

There are two ways of adding columns in nonclustered indexes to make covering indexes.

1-      Adding only key columns in the indexes.

2-       Adding non key columns in the indexes.

Here, I am focusing only on first way i.e. Adding only key columns in the indexes.

Let me explain this with the practical example. First create a table with the script as shown below:

CREATE TABLE [dbo].[xtCheck](
    [stdid] [int] NOT NULL,
    [Test1] [int] NOT NULL,
    [Test2] [int] NOT NULL,
    [Test3] [int] NOT NULL,
    [review] [varchar](max) NULL
) ON [PRIMARY]

Now insert the data in the above table for the testing purpose with the below mention script:

declare @i int
declare @marks int
set @i=1
set @marks=40
while @i<10000
begin
insert into xtCheck values(@i,@marks,@marks,@marks,'no description')
if (@marks=99)
  set @marks=40
else
  set @marks=@marks + 1;
set @i=@i+1
end

Now just create an index on columns test1 and another clustered index on column stdid with the below mention script:

create clustered index IXC_xtCheck_xtdid on xtCheck(stdid)
go
create nonclustered index IX_xtCheck on xtCheck(test1)

now when we run below query including actual execution plan from SSMS:

set statistics io on
select test1,test2 from xtCheck where stdid<400 and Test1=45  
set statistics io off

statistics output is:

Table ‘xtCheck’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution plan is:

In my above select query I just want to get values from test1 and test2 while using where clause on test1 and stdid. We just created an index only on test1. Here the index store values of test1 as well as values of stdid as pointer from nonclustered index to clustered index. So for the above select statement values of test1 gets from index pages while to get the values of test2 sql server make a jump from index pages to base table(which is known as key lookup as shown in above ecxecution plan), which includes the more io cost.

We can reduce this cost by making above index as covering index for above select statement.Here we just add that column to our index for which column sql server use key lookup. Modify the above index as shown below:

create nonclustered index IX_xtCheck on xtCheck(test1,test2)
WITH (drop_existing=on)

now the index IX_xtCheck contain values of both the columns test1 and test2, so result for the above select query can be get from index pages only, i.e. no need to jump from index pages to base table(i.e key lookup). Now run the select query:

set statistics io on
select test1,test2 from xtCheck where stdid<400 and Test1=45  
set statistics io off

statistics output is:

Table ‘xtCheck’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The Actual Execution plan is:

Hence in the above example nonclustered index IX_xtCheck working as a covering index for the select statement

select test1,test2 from xtCheck where stdid<400 and Test1=45. Covering index reduce io cost also as shown in the above example i.e. logical reads reduced from 16 to only 2.

Limitations:

Maximum columns per index key : 16

Maximum Bytes per index key : 900

Covering Indexes by adding key columns boost up the data retrieval but they can slow down the DML queries. So while using covering indexes, consider this thing in your mind.

In my next blog, I will explain the second way of adding columns to make covering indexes i.e. Adding Non Key columns in the indexes.

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook