Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Column Order in an Index Expand / Collapse
Author
Message
Posted Monday, November 22, 2010 8:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 4:07 AM
Points: 357, Visits: 1,682
Comments posted to this topic are about the item Column Order in an Index

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #1024819
Posted Monday, November 22, 2010 9:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 16, 2010 6:47 PM
Points: 2, Visits: 7
A good article.. :)
Post #1024829
Posted Tuesday, November 23, 2010 12:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:02 AM
Points: 1,020, Visits: 1,290
Nice post

Thanks
Post #1024887
Posted Tuesday, November 23, 2010 12:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:51 AM
Points: 928, Visits: 1,112
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
Posted Tuesday, November 23, 2010 1:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 2, 2014 3:39 AM
Points: 350, Visits: 475
Previous link can be found here :
http://sqlserverpedia.com/wiki/Index_Selectivity_and_Column_Order


Franky L.
Post #1024899
Posted Tuesday, November 23, 2010 1:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Tuesday, November 23, 2010 2:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
Sorry, you shouldn't be writing SQL if you think this isn't patently obvious.
Post #1024937
Posted Tuesday, November 23, 2010 3:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, November 23, 2010 4:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Tuesday, November 23, 2010 4:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 15,517, Visits: 27,898
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse