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:

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

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

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

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:

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:

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

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