July 1, 2011 at 11:02 am
Syed Jahanzaib Bin hassan (7/1/2011)
Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2
What is your assumption based on? Please provide more details.
Based on what I can see, the leaf level of the clustered index will include col_1, col_2, col_3 and col_4 and the nonclustered index will only have col_1 and col_2. So how those can be identical?
Also, please explain how including the clustered index column into a nonclustered index will add any overhead. If you don't mind, also explain how to perform a Bookmark Lookup without having the clustered index column(s) (I'm not talking about an RID lookup on a heap though).
July 1, 2011 at 11:03 am
GilaMonster (7/1/2011)
GSquared (7/1/2011)
The main advantage I see to the non-clustered index in this case, is that it will have more rows per page than the clustered index (since it's narrower), and if it's got everything you need for a query (covering index), then you're using less I/O, and less RAM, because of less pages.If the table was wider I'd agree with you, but 4 byte row (+ header & other bits) vs 12 byte row (+ header & other bits), not likely to make that much difference (unless we're talking billions of rows)
Yeah, but it's the only advantage I can see at all to that index. Probably not significant in most cases, but could be in some.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2011 at 11:17 am
Thanks to all who answered. It is truly appreciated.
Chris
July 1, 2011 at 11:20 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 4, 2011 at 12:07 am
No it's not. SQL includes the clustering key in all nonclustered indexes anyway whether you specify it or not
I agreed,but there is no need to mentioned in the nonclustered index,it will not take pointer space in the nonclustered index file ?
I don't agree with that. These are not the same. It depends on the query.
you can use sp_helpindex8 from the http://www.SQLSKILLs.com then it will give you an idea about leaf level of the index then you can remove or add indexes with the help of this new helpful store procedure
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 4, 2011 at 1:17 am
Syed Jahanzaib Bin hassan (7/4/2011)
No it's not. SQL includes the clustering key in all nonclustered indexes anyway whether you specify it or not
I agreed,but there is no need to mentioned in the nonclustered index,it will not take pointer space in the nonclustered index file ?
If it's needed it should be mentioned. In a scenario like the following the clustering key CANNNOT be omitted without changing the definition of the index:
CREATE CLUSTERED INDEX idx_C on T (Col1)
CREATE NONCLUSTERED INDEX idx_1 ON T (Col1, Col2)
In that case (which matches the OP's question) omitting Col1 from the nonclustered index changes the index's definition.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2011 at 5:23 am
CREATE NONCLUSTERED INDEX idx_1 ON T (Col1, Col2)
the leaf level will be Col1,Col2
CREATE NONCLUSTERED INDEX idx_1 ON T (Col2)
the leaf level will be same too as Col2,Col1
for the Leaf Level of this current scenerio
http://sqlconsultant.files.wordpress.com/2011/07/leaf-level.jpg
for the sp_helpindex8
http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx
this SP is very helpfull to remove duplicate indexes with the help of
[column in tree]
[column in leaf]
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 4, 2011 at 8:28 am
Syed Jahanzaib Bin hassan (7/4/2011)
CREATE NONCLUSTERED INDEX idx_1 ON T (Col1, Col2)
the leaf level will be Col1,Col2
CREATE NONCLUSTERED INDEX idx_1 ON T (Col2)
the leaf level will be same too as Col2,Col1
An index on Col1, Col2 is not the same as an index on Col2, Col1. Column order in indexes matters a lot. They are different indexes with different usages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2011 at 6:35 am
Syed Jahanzaib Bin hassan (7/4/2011)
CREATE NONCLUSTERED INDEX idx_1 ON T (Col1, Col2)
the leaf level will be Col1,Col2
CREATE NONCLUSTERED INDEX idx_1 ON T (Col2)
the leaf level will be same too as Col2,Col1
for the Leaf Level of this current scenerio
http://sqlconsultant.files.wordpress.com/2011/07/leaf-level.jpg
for the sp_helpindex8
http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx
this SP is very helpfull to remove duplicate indexes with the help of
[column in tree]
[column in leaf]
If the selectivity on Col2 is different than Col1, then those two indexes will NOT perform the same in many cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply