September 25, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/worstpracticesortingbyordinal.asp>http://www.sqlservercentral.com/columnists/awarren/worstpracticesortingbyordinal.asp
September 30, 2002 at 7:10 am
I don't like the ordinal order by, but for a different reason. I would hope that if you are altering the Select portion, you'd have enough sense to look at the entire statement anyway.
I don't like it because I like code to be self documenting where ever possible. Order by CustomerNum, DateInvoiced is a hell of a lot clearer than Order by 3, 4 in my book. I could get out another soapbox and talk about using clearly named field names, but that belongs in someone else's article!
Just for discussion purposes, has anyone looked at any performance differences using ordinals? I haven't, and unless it was very significant, I still wouldn't use them due to the clarity issue.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
September 30, 2002 at 9:16 am
Totally agree. I'd be afraid to test for fear it would be faster!
Andy
October 1, 2002 at 4:00 am
My opinion is better safe than sorry. So what if you have to do more maintainence, if someone comes along later who doesn't understand ordinal order by's then you end up causing the enduser grief if he displaces a column that was sorted on and should remain that way. And anyway, how often would you really want to change the order by.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 1, 2002 at 6:47 am
It is a worst practice when it comes to production class code.
But it sure is a power users tool and a shortcut for ad hoc queries. When I am working on a problem that requires me to run a bunch of SELECT statements, I am not going to type the column names in ORDER BY clause. I'll simply use ordinal numbers.
Here's another reason why, we shouldn't use ordinal position in production code:
SELECT id, name
FROM sysobjects
ORDER BY 1.2
Notice the dot between 1 and 2. It's a typo, it should actually be a comma. But the statement still works, except that the output is not sorted as you'd expect, there by introducing a bug.
And no, there are no performance differences.
HTH,
Vyas
Edited by - vyaskn on 10/01/2002 07:03:08 AM
HTH,
Vyas
SQL Server MVP
http://vyaskn.tripod.com/
October 1, 2002 at 6:07 pm
Thanks for the comments!
Andy
October 7, 2002 at 10:49 am
Actually, I didn't know there's such a statement. I have lots of sql books and none of them ever mentions such statement. Maybe because it's a bad, bad one.
Anyways, thanks for making a newbie aware of it.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
November 2, 2006 at 7:23 pm
i think it will not hurt to use it for small tables.
January 1, 2007 at 11:52 am
I have on occasion used an ordinal for sorting, only for adhoc stuff though, not production code.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply