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:

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:

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 Twitter |  Follow me on FaceBook