SQLServerCentral Article

Worst Practice - Sorting by Ordinal


Worst practices - things that range from annoying you slightly to driving you

to absolutely crazy. This one falls on the milder side (depending on how your

week is going). Ever run across a query that looks like this?

Select field1, field2, field3 from pubs order by 1,


It really should look like this:

Select field1, field2, field3 from pubs order by

field1, field2

I've heard it variously described as a power users tool (if they change the

select they don't have to change the order by too) and a "shortcut" -

meaning its faster to type 1, 2 than it is to spell out the field names. Give me

a break!

Break is what you're liable to get when something changes. Maintenance work

done on the select changes the field order, which changes the order by, which

changes....how the indexes get used! This is one of those so subtle gotchas that

can have you spend a lot of time looking for problems where none used to exist.

But wait...we could use our old friend Select *, then no one would have a

reason to do maintenance work! We'll debate the merits of select * another day,

for now consider that there are still users out there who insist on doing things

like alphabetizing their column names in EM. You wind up with exactly the same

problem. The order of the fields in the select changes, that changes

Interested in other Worst Practice articles? Follow this link.

As always, I look forward to your comments on this article. Especially if you

disagree! Nothing better for our readers than to see both sides of the story!


5 (1)

You rated this post out of 5. Change rating




5 (1)

You rated this post out of 5. Change rating