September 5, 2007 at 10:37 am
All,
I heard somewhere (and now I can't find the source) that using Column Names in an Order By clause was more efficient than using position integers. Does anyone know for a fact if this is true or not? And if so, can someone point me to some relevant source material?
Example: Select A, B from MyTable Order by A
or
Example: Select A, B from MyTable Order by 1
Thanks!
September 5, 2007 at 11:34 am
I'd like to see that source too. I don't see any reason for any version to be any faster. In fact, the numeral version will be depreciated soon.
September 5, 2007 at 11:46 am
Maybe that's what I saw. Information on the numeral version being depreciated... Ninja, do you have a source for that?
Thanks,
September 5, 2007 at 1:19 pm
Books online, upgrade advisor.
September 5, 2007 at 10:08 pm
As for one being faster than the other, I would suspect that the answer is that they're exactly the same as the optimizer is going to do it's thing either way?
Joe
September 6, 2007 at 5:42 am
Unfortunately, the Upgrade Advisor article in BOL doesn't actually say what might be depreciated in future versions of SQL Server. And I actually need quotable text to give to the higher ups to justify why I'm going to ask people to stop using "Order by <number>".
I knew I had seen something about either efficiency or depreciation of this method somewhere. I just can't find the stupid link and GOOGLE isn't being terribly helpful...
Oh, well. I guess I'll have to wait to get everyone on the same page until they actually do depreciate the code.
September 6, 2007 at 8:35 am
If you believe Joe Celko the use of positional column numbers has been deprecated in standard sql:
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
September 6, 2007 at 2:46 pm
I personally think that it is less efficient to use the numeric order by when you are maintaining code. You are less likely to have to go look up which field is in that position, thus saving you time.
Also, what about the outside chance that order of the fields in the structure could be changed (not a good idea). I wonder how many hours (days?) it would take to track that bug down.
Steve
September 7, 2007 at 4:43 am
AHA! Now that's an argument I can use.
Thanks, Steve. @=)
Darnit... Can't get the quote thing to quote the line I wanted to quote... Meant to quote "Also, what about the outside chance that order of the fields in the structure could be changed (not a good idea).", which is the argument I can use...
September 7, 2007 at 7:01 am
The more I think about it, all it takes is dropping a field that comes before the ordinal number used. Then all the numbers after that field are shifted by one. Maybe that chance is not quite so "outside" after all.
Steve
September 7, 2007 at 7:06 am
nevermind the fun when your order by field is the last one, and then you drop a field.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply