sql_server_covering_index_2: Adding Non Key Columns

Hi Friends,

This blog is the continuation of my previous blog on Use of Covering Indexes here is the link

Today here I will explain Adding non key columns in the nonclustered indexes as well as what are the benefits of adding non key columns in comparison to adding key columns in nonclustered indexes.

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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xtCheck]') AND type in (N'U'))
DROP TABLE [dbo].[xtCheck]
GO
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 query including actual execution plan from SSMS:

set statistics io on
select test1,test2,review 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_2_Adding_Non_Key_Columns

Now to just reduce the cost of above key lookup, alter the above index:

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

But when we run this query SSMS gives error:

Column ‘review’ in table ‘xtCheck’ is of a type that is invalid for use as a key column in an index.

This is just because of the limitation of Index key columns “We can’t use a column having data type varchar(max) as key column in an index.”

So how can we increase the performance here. Create the index and include this column as non key column using INCLUDE option:

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

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

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

statistics output is:

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

Execution plan is:

2_sql_server_covering_index_2_Adding_Non_Key_Columns

Here we have seen performance improvement as elimination of key lookup and logical reads improved from 16 to only 4.

Storage of Non key columns:

Key columns in nonclustered index stored at all levels while non key columns stored only at leaf levels.

Some of the limitations of non key columns are:

Data types text, ntext, image are not allowed as non key columns.

The maximum number of non key columns are 1023.

Non key columns require more disk storage.

 

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

Avatar

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_2: Adding Non Key Columns”

Leave a Reply

Your email address will not be published. Required fields are marked *