Column Order in an Index

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sarvesh Singh » Column Order in an Index

42 posts, Page 1 of 5

1 2 3 4 5 »»»

Column Order in an Index

Rate Topic

Display Mode

Topic Options

Author

Message

ss-457805

Posted Monday, November 22, 2010 8:35 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:14 AM
Points: 356, Visits: 1,658

Comments posted to this topic are about the item Column Order in an Index

blog: http://sarveshsingh.com

Twitter: @sarveshsing

Post #1024819

faelconn1ck

Posted Monday, November 22, 2010 9:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 16, 2010 6:47 PM
Points: 2, Visits: 7

A good article.. :)

Post #1024829

Hardy21

Posted Tuesday, November 23, 2010 12:49 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:42 PM
Points: 877, Visits: 1,158

Nice post

Thanks

Post #1024887

Wilfred van Dijk

Posted Tuesday, November 23, 2010 12:52 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:56 AM
Points: 898, Visits: 1,045

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/

Wilfred
The best things in life are the simple things

Post #1024889

Franky Leeuwerck

Posted Tuesday, November 23, 2010 1:17 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:43 AM
Points: 349, Visits: 467

Previous link can be found here :
http://sqlserverpedia.com/wiki/Index_Selectivity_and_Column_Order

Franky L.

Post #1024899

Michael Ebaya

Posted Tuesday, November 23, 2010 1:50 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, November 27, 2010 10:41 PM
Points: 58, Visits: 113

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.

Post #1024908

paul s-306273

Posted Tuesday, November 23, 2010 2:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:24 AM
Points: 1,158, Visits: 642

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

Post #1024937

Kiran Khot

Posted Tuesday, November 23, 2010 3:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 8:02 AM
Points: 2, Visits: 8

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

Post #1024960

Michael Ebaya

Posted Tuesday, November 23, 2010 4:05 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, November 27, 2010 10:41 PM
Points: 58, Visits: 113

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.

Post #1024998

Grant Fritchey

Posted Tuesday, November 23, 2010 4:23 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 13,371, Visits: 25,148

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1025014

« Prev Topic | Next Topic »

42 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions