Column Order in an Index

Question

Post reply

Column Order in an Index

ss-457805

SSCertifiable

Points: 5872
More actions
November 22, 2010 at 8:35 pm

#241575

Comments posted to this topic are about the item Column Order in an Index
blog: http://sarveshsingh.com Twitter: @sarveshsing

Viewing 15 posts - 1 through 15 (of 48 total)

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

faelconn1ck Valued Member Points: 66 More actions · Answer 1

faelconn1ck

Valued Member

Points: 66

November 22, 2010 at 9:43 pm

#1254164

A good article.. 🙂

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Hardy21

SSCrazy Eights

Points: 9708

November 23, 2010 at 12:49 am

#1254211

Nice post 🙂

Thanks

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 3

Poor summary about column considerations in an index. For the complete picture, I suggest http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/">

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

Wilfred
The best things in life are the simple things

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 4

Previous link can be found here :

http://sqlserverpedia.com/wiki/Index_Selectivity_and_Column_Order

Franky L.

Michael Ebaya Mr or Mrs. 500 Points: 564 More actions · Answer 5

An article so absolutely shallow and superficial as to be entirely braindead. I won't go into why (if you don't already know, then you shouldn't be writing SQL code in the first place) but I will correct one error. An index scan is not "much better" than a table scan. It's exactly the same cost as scanning a table as wide as that index. In this example, if the table had no other rows than last name and first name, the cost would be identical whether table or index scanning, and the QO would only favor the index scan on the assumption that the index is more likely to already be cached.

paul s-306273 SSChampion Points: 10730 More actions · Answer 6

Sorry, you shouldn't be writing SQL if you think this isn't patently obvious.

Kiran Khot SSC Journeyman Points: 84 More actions · Answer 7

Further to your scenario, Can you help me with your feedback and comments on the below scenario

Considering the same scenario as yours

Scenario 1: I have one more index lastname , emailid then when i fire a query then how will be the execution plan be for following WHERE clauses

1. WHERE LastName='ABC'

2. WHERE LastName='ABC' AND EMAILid ='abc@xyz.com'

3. WHERE LastName='ABC' AND EMAILid LIKE 'abc@%'

4. WHERE LastName='ABC' AND firstName ='PQR'

Thanks in advance

Regards,

Kiran R. Khot

Michael Ebaya Mr or Mrs. 500 Points: 564 More actions · Answer 8

Kiran, B-trees really aren't that complex to understand. In all four of your examples, there will be an index seek. In case 1 and 4, the index will then be range-scanned for all ABC values (in case #1, this is optimal anyway). In case 2 and 3, the second WHERE predicate is also an index key, so the seek will proceed directly to the first matching value from the result set.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 9

Thanks for the article Sarvesh. You may want to spend a little more time on the next one, just nail down why these things occur. As you showed, it's not simply the order in which columns are stored, rather, it's which column is on the front, the leading edge, of the index. Answer the question, why does the leading edge matter, and you'll have a much improved article.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

vishalkumar_dobariya SSC Enthusiast Points: 175 More actions · Answer 10

vishalkumar_dobariya

SSC Enthusiast

Points: 175

November 23, 2010 at 4:47 am

#1254329

Good Article to understand Non clustered index usage.

Ukon SSC Eights! Points: 920 More actions · Answer 11

The article may be short and short on detail, however, the really good thing was that it lead me to this discussion and the links here. Thank you!:-)

ss-457805 SSCertifiable Points: 5872 More actions · Answer 12

Thanks for the feedback Grant. Much appreciated. Will keep that in mind for my next article.

blog: http://sarveshsingh.com Twitter: @sarveshsing

sjimmo SSChampion Points: 11139 More actions · Answer 13

Sarvesh, This was a good article, with the potential to be much better if you were to expand on it. You leave out a lot of why things work this way leaving it at a very entry level atricle.

I think it great that you did the article, but please - put more effort into it. Why does the index work this way? How do statistics impact it? What is the difference between an index scan and an index seek? etc...

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

cengland0 SSCertifiable Points: 6102 More actions · Answer 14

Please don't laugh at me for asking this question.

Why would you create a compound index with both last name and first name together? Why not two separate indexes where one is for last name and the other is for first name. That should handle any combination that you have in your where clause.