Unnecessary Index?

Question

Post reply

Unnecessary Index?

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 2

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

croberts 36762 SSC Eights! Points: 942 More actions · Answer 3

croberts 36762

SSC Eights!

Points: 942

July 1, 2011 at 11:17 am

#1347218

Thanks to all who answered. It is truly appreciated.

Chris

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

SQLRNNR

SSC Guru

Points: 281324

July 1, 2011 at 11:20 am

#1347225

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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 5

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

My Blog
www.aureus-salah.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 7

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]

My Blog
www.aureus-salah.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 9

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