|
|
|
SSCertifiable
Group: Moderators
Last Login: Saturday, February 04, 2012 8:26 AM
Points: 6,458,
Visits: 1,368
|
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, October 10, 2011 2:00 PM
Points: 297,
Visits: 329
|
|
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
|
|
|
|
|
SSCertifiable
Group: Moderators
Last Login: Saturday, February 04, 2012 8:26 AM
Points: 6,458,
Visits: 1,368
|
|
|
|
|
|
SSCrazy Eights
Group: Moderators
Last Login: Wednesday, September 07, 2011 7:27 AM
Points: 8,313,
Visits: 554
|
|
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)
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Tuesday, March 30, 2010 4:25 AM
Points: 64,
Visits: 6
|
|
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
http://vyaskn.tripod.com/
Edited by - vyaskn on 10/01/2002 07:03:08 AM
HTH,
Vyas
SQL Server MVP
http://vyaskn.tripod.com/
|
|
|
|
|
SSCertifiable
Group: Moderators
Last Login: Saturday, February 04, 2012 8:26 AM
Points: 6,458,
Visits: 1,368
|
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Monday, June 06, 2011 10:53 AM
Points: 52,
Visits: 68
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, November 07, 2006 11:29 PM
Points: 2,
Visits: 1
|
|
| i think it will not hurt to use it for small tables.
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Tuesday, December 20, 2011 2:48 AM
Points: 717,
Visits: 243
|
|
I have on occasion used an ordinal for sorting. Not for laziness of typing a field name, but if i wanted to sort by the results of a column that contains a subquery. So far as I've found, SQL Server doesn't allow me to specify a column alias for a subquery as a column in the order by. The same effect could be achieved by rewriting the subquery in the order by, but i found it to be clearer to specify the column number.
I've never run a performance check - does anyone know if there is a performance difference between the two approaches with the subquery column?
|
|
|
|
