A few weeks ago, I wrote a post about forming a new habit: always terminate statements with semi-colons. Today I thought I would start a series on kicking bad habits that many of us have developed over time. I provided a little more background on this series over on my company's site. Today's topic: using ordinal numbers in our ORDER BY clauses.
At least once a week, I catch myself using ordinal position to define order. For example, I might have this:
SELECT foo, bar
FROM dbo.splunge
ORDER BY 1, 2 DESC; |
This is lazy shorthand, and is bound to get screwed up at some point unless I have encrypted the stored procedure and threw away the key. Invariably, someone at some point is going to come in and add a column, remove a column, or change the order of the columns.
Remember that a lot of us produce stored procedures to be consumed by essentially "dumb" clients - Windows forms or web applications that lack the ability to perform any ordering on their own. So often the ORDER BY clause of a resultset coming back from a stored procedure is pretty important. When editing a stored procedure that has an error-prone ORDER BY clause like the above, it is very easy to make changes to the SELECT list without even looking at the ORDER BY clause, or the reverse. Imagine the web developer using the above procedure asks to change the SELECT list. Now he wants mort, foo, bar. (Note that order of the columns themselves should be irrelevant to the application, since it should be using column name and not ordinal references, but in some cases we like certain columns to appear in a certain order when we are debugging in SSMS and the like.) So assume you do exactly as the developer asks, and edit the SELECT clause and alter the procedure:
SELECT mort, foo, bar
FROM dbo.splunge
ORDER BY 1, 2 DESC; |
Oops! Now the developer is complaining that his UI is broken because the order is all messed up. This would not have happened if you had used the following syntax in the first place:
SELECT mort, foo, bar
FROM dbo.splunge
ORDER BY foo, bar DESC; |
I know it is hard to spell out those column names when you're doing ad
hoc stuff, but getting in the habit will potentially save your bacon
someday. And you can't use the excuse that you have very complicated expressions in your SELECT list, because ORDER BY is the only place in the entire query where you can reference an alias you provided. So the following is completely legal:
SELECT foo = RTRIM(CONVERT(INT, ABS(foo) % 2))
FROM dbo.splunge
ORDER BY foo; |
There is nothing lost by being explicit, except for a few keystrokes. But there is plenty to lose by not doing so - including some patience for the developer running to your cube like their head is on fire. Ideally, when you are making changes to existing code, you examine the entire statement to ensure consistency. But try to be proactive and protect yourself against the inevitable rushed and possibly botched emergency fix.
I am working on a series of "Bad habits to kick" articles, in an effort to motivate people to drop some of the things that I hate to see when I inherit code. Up next: using loops to populate large tables.