Worst Practice - Sorting by Ordinal

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Worst Practice - Sorting by Ordinal

Worst Practice - Sorting by Ordinal

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Wednesday, September 25, 2002 12:00 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #7025

BobAtDBS

Posted Monday, September 30, 2002 7:10 AM

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

Post #42384

Andy Warren

Posted Monday, September 30, 2002 9:16 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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

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

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #42385

Antares686

Posted Tuesday, October 01, 2002 4:00 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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

vyaskn

Posted Tuesday, October 01, 2002 6:47 AM

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/

Post #42387

Andy Warren

Posted Tuesday, October 01, 2002 6:07 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Thanks for the comments!

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

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #42388

swarm

Posted Monday, October 07, 2002 10:49 AM

Valued Member

Group: General Forum Members
Last Login: Monday, July 09, 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

troy fernando

Posted Thursday, November 02, 2006 7:23 PM

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.

Post #320161

T. C.

Posted Monday, January 01, 2007 11:52 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, May 09, 2013 5:54 AM
Points: 737, Visits: 336

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

Post #333649

« Prev Topic | Next Topic »

Permissions