See the full index.
At least once a week, I catch myself using ordinal position to define order. For example, I might have this:
SELECT foo, bar = COUNT(*) 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 thrown 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, and the order is going to be wrong. Incorrect order may not be a big deal, but it can be disastrous.
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.
Now, assume you do exactly as the developer asks, and edit the
SELECT clause and alter the procedure:
SELECT mort, foo, bar = COUNT(*) 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 = COUNT(*) FROM dbo.splunge ORDER BY foo, bar DESC;
I know it is cumbersome 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 you can reference the alias in the
ORDER BY. As above, the following is completely legal:
SELECT blat = RTRIM(CONVERT(int, ABS(foo) % 2)) FROM dbo.splunge ORDER BY blat;
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.
See the full index.