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

Worst Practice - Sorting by Ordinal Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2002 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:27 AM
Points: 6,800, Visits: 1,917
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #7025
Posted Monday, September 30, 2002 7:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 297, Visits: 335
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
Post #42384
Posted Monday, September 30, 2002 9:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:27 AM
Points: 6,800, Visits: 1,917
Totally agree. I'd be afraid to test for fear it would be faster!

Andy
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #42385
Posted Tuesday, October 1, 2002 4:00 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, November 14, 2014 10:06 AM
Points: 8,370, Visits: 745
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)



Post #42386
Posted Tuesday, October 1, 2002 6:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, February 22, 2014 2:31 AM
Points: 64, Visits: 8
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/
Post #42387
Posted Tuesday, October 1, 2002 6:07 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:27 AM
Points: 6,800, Visits: 1,917
Thanks for the comments!

Andy
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #42388
Posted Monday, October 7, 2002 10:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 9, 2012 5:15 PM
Points: 52, Visits: 71
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
Post #42389
Posted Thursday, November 2, 2006 7:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 7, 2006 11:29 PM
Points: 2, Visits: 1
i think it will not hurt to use it for small tables.
Post #320161
Posted Monday, January 1, 2007 11:52 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:33 AM
Points: 741, Visits: 377
I have on occasion used an ordinal for sorting, only for adhoc stuff though, not production code.
Post #333649
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse