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 Saturday, November 27, 2010 10:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 11:09 AM
Points: 48, Visits: 70
I agree with the "spend a little more time with the article" focus.When i first read the topic, i got interested, and it was an interesting read, but i do think it also was a little bit shallow and with some lack of research, even more if you consider you're talking about, i think, one of the more complex topics on database development.

It's just constructive criticism.

Regards
DG
Post #1027018
Posted Saturday, May 11, 2013 7:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 3, 2014 1:08 AM
Points: 543, Visits: 201
Nice attempt. Here in this article you should explain why SQL server does like this. I'm referring to the Index Statistitics. You can also mention in the article that if all of the columns are used as predicate, order doesn't matter more precisly it is about the leading column of the index matters not the order.
Post #1451826
Posted Friday, July 5, 2013 1:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 42,768, Visits: 35,867
sandippani (5/11/2013)
Nice attempt. Here in this article you should explain why SQL server does like this. I'm referring to the Index Statistitics.


It's not due to statistics. It's due to the fact that you can only seek on a left-based subset of the index key.

You can also mention in the article that if all of the columns are used as predicate, order doesn't matter more precisly it is about the leading column of the index matters not the order.


The order still matters, especially if some of the predicates are inequalities.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1470612
Posted Friday, July 5, 2013 5:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:40 AM
Points: 336, Visits: 940
I'm getting a little tired of all the jerkoffs who immediately jump in with comments like "You shouldn't be writing SQL code." Do you suppose anyone believes you were born with the ability to write perfect code? That's how people learn - try, make mistakes, try again, read, ask questions, post comments, listen to advice, tune your skills, swap ideas. If nobody did anything until they were perfect at it, nothing would ever get done, anywhere, by anybody.

Get over yourselves - you've made mistakes and written garbage code too, we all have. The trick is to learn from it, and if you're prepared for possible admission to the human race, help others learn as well.

Good manners are a nice plus, but if you can't post something that's at least helpful, go troll under someone else's bridge.
Post #1470680
Posted Friday, July 5, 2013 5:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:40 AM
Points: 336, Visits: 940
One thing I didn't see anyone mention is size. An index is likely (not necessarily, of course) smaller than the table, so even in the worst case of a full scan of the index, the amount of data passed around from the disk subsystem should be smaller. Seems like that should be worth something.
Post #1470684
Posted Friday, July 5, 2013 1:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 92, Visits: 1,230
David Walker-278941 (11/23/2010)
"This does not mean that the index 'NonClust_Contact_Name' was not useful. It's still much better than the Clustered scan."

*WHAT* is still much better than the clustered scan? I'm confused by that last sentence.


I'm confused about this comment as well....

Since we aren't shown a table definition with details about the clustered index, the only thing I could come up with is a scenario where the clustered index is "wider" (contains more data) than the non-clustered index. Thus the non-clustered index would be smaller and therefore preferred from an efficiency perspective. Maybe. If this was a heap would the RID be smaller, and the optimizer prefer a table scan? I don't know...


Post #1470846
Posted Sunday, July 7, 2013 10:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
pdanes (7/5/2013)
I'm getting a little tired of all the jerkoffs who immediately jump in with comments like "You shouldn't be writing SQL code." Do you suppose anyone believes you were born with the ability to write perfect code? That's how people learn - try, make mistakes, try again, read, ask questions, post comments, listen to advice, tune your skills, swap ideas. If nobody did anything until they were perfect at it, nothing would ever get done, anywhere, by anybody.

Get over yourselves - you've made mistakes and written garbage code too, we all have. The trick is to learn from it, and if you're prepared for possible admission to the human race, help others learn as well.

Good manners are a nice plus, but if you can't post something that's at least helpful, go troll under someone else's bridge.


+1

Anyone posting an article or script to be published for the entire community to benefit from (or to be nit picked incessantly) deserves to be treated with respect. Even if an author gets it quite wrong, if the follow-up discussion is professional and without personal attacks on a persons character or education then we can still learn something.

For anyone who thinks writing a submissible article or script is easy, I can say from experience that it's not. So to Sarvesh Singh I say keep trying and don't get discouraged!

 
Post #1471031
Posted Sunday, July 7, 2013 11:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 7, 2013 7:05 AM
Points: 1, Visits: 12
On what scenarios we find this Index? Will it reduce time complexity if we use it?

Please provide some more examples?
Post #1471041
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse