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

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

Nonclustered Index in SQL Server

After finishing two articles on Index “Somebasics about Index in SQL Server” and “ClusteredIndex in SQL Server”, this is now time to explore nonclustered index in SQLServer. There are only two major difference between Clustered and NonclusteredIndex are as follows:
1.)    Youcan have only one Clustered index per table and 249 (till SQL Server 2005) and999 (after SQL Server 2005) Nonclustered Index per table
2.)    ClusteredIndex stores actual row data in the leaf level and nonclustered index wouldstores only those columns which are included in nonclustered index and pointerto the actual row which may be in clustered index or in heap (know more aboutleaf, heap, RID etc. from here).
Like clustered index, you can have one or multiple columnsdefined in Nonclustered Index too. Order of the column defined in Nonclusteredplays an important role to meet Index seek, as I told you in previous articletoo that Index seek is good than Index Scan, so one should try to meet up Indexseek as long as possible.
We are going to use same database and “Orders” table definedin previous article “ClusteredIndex in SQL Server”.
–creatingnonclustered index,
CREATE NONCLUSTERED INDEXidx_orderdate on Orders(orderdate,orderid)
–run followingquery with execution plan and see the results in execution plan
–you can seeexecution plan with the following steps
–first selectbelow given query
–Press Ctrl+M
–press F5
SELECT OrderDate,orderid from orders where OrderDate = ‘2010-02-01 00:04:00.000’
You will see your nonClustered Index Seek in your executionplan.
 
There are few more indexes under the same category likeINCLUDE columns, Filtered Index, Covering Index which we will be exploring verysoon.

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 defaultreference of all articles but examples and explanations prepared by RiteshShah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile