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:

1_SQL_Server_Covering_Index_1

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:

2_SQL_Server_Covering_Index_1

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 TwitterFollow me on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

2 Comments on “SQL Server Covering Index 1”

  1. It’s a good article and simple to understand.

    In my point of view related to this article:

    1. It will be more effective, if the author mentioned about the technology or architecture behind the cover index and how the cover index works and store. May be it will comes on the second editions.

    2. I always prefer to provide version number. Such cover Index comes from MS SQL Server 2005 onwards. If the version no is not provided, someone any confuse with MS SQL Server 2000.

    3. It will be better to discuss about new feature related to Covered Index on MS SQL Server 2012 if there is any or unchanged…etc

    Hope, in second additions author must take care of that.

  2. Thanks JOYDEEP, i will consider these things in my next blog.

    Thanks & Regards:

    Prince Kumar Rastogi

Leave a Reply

Your email address will not be published.