Included Column Index with non clustered index in SQL Server 2005/2008/Denali

My previous article was based on “Non clustered Index” and this article focuses on “Included Column” feature of Index which was introduced in SQL Server 2005 and still there in newer version.

As long as architecture of “Included Column” concerns, whatever columns you have defined under “Include” clause under the index, those will be stored on the Leaf pages, it won’t get stored on the Root page or Intermediate page of the index.

Now, let us talk little bit about the benefit we are going to get out of this feature.

The main feature is that, the columns you have under “Include” clause of “Create Index” statement would not affect the size of the index. Index has limitation that you can have it on maximum of 16 column / 900 bytes. So no matter how big columns you are going to use in your “Include”, you will get benefit for sure.

You should keep those columns in “Include” clause which generally comes under “SELECT” clause and not being used much in “WHERE”, “GROUP BY” or “ON” clause of “JOIN”.

We are going to create one table with big column size and will try to create Non Clustered Index on that.

--create dummy table and see whether we are able to create index on that or not.

create table NonClustCheck

(

Col1 char(500),

col2 char(400),

col3 char(10)

)

GO

--if you will try following idex, you will greeted with error message as follows:

--Msg 1944, Level 16, State 1, Line 1

--Index 'idx_NonClustCheck' was not created. This index has a key length of at least 910 bytes.

--The maximum permissible key length is 900 bytes.

create nonclustered index idx_NonClustCheck on NonClustCheck(col1,col2,col3)

go

--you can create following index on the same table

create nonclustered index idx_NonClustCheck1 on NonClustCheck(col1)

INCLUDE(col2,col3)

go

we finally we have created non clustered index with one key column (Col1) and two included column (Col2 and Col3). Let us check whether this index gets scan or seek or optimizer decides not to use this.

--well there is no data in this table yet,

--even just wanted to see whether Non clustered index is having any effect or not.

--run following query with execution plan and you can see Index Seek

-- Ctrl + M and than F5 to run query with execution plan.

select * from NonClustCheck where Col1='SQLHub.Com'

GO

--you can see Col2 is not as the Index Key,

--even you can see that our non clustered index is getting SCAN.

select * from NonClustCheck where Col2='hello'

GO

Hope this will be helpful to you. Do drop comments; it will encourage me for sure.

if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”