Worst Practice - Sorting by Ordinal

  • 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

  • Totally agree. I'd be afraid to test for fear it would be faster!



  • 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)

  • 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.




    Edited by - vyaskn on 10/01/2002 07:03:08 AM

    SQL Server MVP

  • 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

  • i think it will not hurt to use it for small tables.

  • I have on occasion used an ordinal for sorting, only for adhoc stuff though, not production code.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply