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

My previous article was based on “Nonclustered Index” and this article focuses on “Included Column” feature ofIndex 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, thosewill be stored on the Leaf pages, it won’t get stored on the Root page orIntermediate page of the index.
Now, let us talk little bit about the benefit we are goingto 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. Sono matter how big columns you are going to use in your “Include”, you will getbenefit for sure.
You should keep those columns in “Include” clause whichgenerally comes under “SELECT” clause and not being used much in “WHERE”, “GROUPBY” or “ON” clause of “JOIN”.
We are going to create one table with big column size andwill try to create Non Clustered Index on that.
–create dummytable 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 willtry 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 akey length of at least 910 bytes.
–The maximum permissible key length is 900 bytes.
create nonclustered indexidx_NonClustCheck on NonClustCheck(col1,col2,col3)
go
–you can createfollowing index on the same table
create nonclustered indexidx_NonClustCheck1 on NonClustCheck(col1)
INCLUDE(col2,col3)
go
we finally we have created non clustered index with one keycolumn (Col1) and two included column (Col2 and Col3). Let us check whetherthis index gets scan or seek or optimizer decides not to use this.
–well there isno data in this table yet,
–even justwanted to see whether Non clustered index is having any effect or not.
–run followingquery with execution plan and you can see Index Seek
— Ctrl + M andthan F5 to run query with execution plan.
select * from NonClustCheck where Col1=‘SQLHub.Com’
GO
–you can seeCol2 is not as the Index Key,
–even you cansee 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 willencourage 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 adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: