Bad habits to kick : ORDER BY ordinal

AaronBertrand — Tue, 06 Oct 2009 14:49:00 GMT

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.

Ladies and gentlemen, start your semi-colons!

AaronBertrand — Thu, 03 Sep 2009 17:13:00 GMT

Almost a year ago now, I wrote up a blog post detailing the things that I strive to do when writing T-SQL modules (mostly stored procedures). One of the things that I have been doing since my very first common table expression (CTE) back in the Yukon beta is ending all relevant statements with statement terminators (semi-colons). A few responses to the above post indicated that they don't currently use statement terminators, and had no interest in starting. Let's quickly recap the reasons why I started making this a habit.

Today, I use CTEs and Service Broker.

Unless it is the first statement in a batch, the previous statement prior to a CTE declaration needs to be terminated with a semi-colon. This is also true for Service Broker commands (e.g. SEND ON CONVERSATION). Some people "fix" this by simply writing code ike this:

;WITH foo AS (...) ...

And typically when I post CTE examples to newsgroups / forums, I do this, to avoid the inevitable comeback with this error message attached (because you never know where they are going to jam your example, and then complain that it didn't work):

.Net SqlClient Data Provider: Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression
or an xmlnamespaces clause, the previous statement must be terminated with a semicolon

But I think that syntax is far too ugly and counter-intuitive for my production code (YMMV). So for this reason, all relevant statements in my code get a semi-colon. I didn't go to the extent of inspecting all of my existing code and adding them, but if I was re-visiting a module for some other enhancement, I would take the few seconds and add any semi-colons that were missing. I didn't feel like I needed to do this, but thought it best to future-proof my code as much as possible, when convenient. And of course when writing new code, I would always terminate. This is probably best demonstrated by scrolling through my blog posts over the past year or two, and trying to spot any examples where I neglected to add a semi-colon. I doubt you'll find many!

Someday, I may use MERGE.

In SQL Server 2008, a similar requirement was added for MERGE: you can't run a MERGE statement unless it is properly terminated with a semi-colon. Otherwise, you get this error message:

.Net SqlClient Data Provider: Msg 10713, Level 15, State 1, Line 21
A MERGE statement must be terminated by a semi-colon (;)

So because of this, the set of reasons for future-proofing your code in this way has doubled: while your CTEs and MERGE statements obviously work today, because your procedure compiled, a user modifying them later might break them, and this will cause an extra debugging step. So at this point, I was debating whether it might make sense to comb through all of my code and add semi-colons where appropriate, but was still sitting quite squarely on the fence.

Not convinced? Hold on, there's more!

In the previous post, I suggested that someday Microsoft will make statement terminators mandatory. Well, they have. Statements that DON'T end with a proper statement terminator are on the deprecation list, which means that at some point, any statement that doesn't end with a semi-colon will cause an error similar to those listed above. Sounds funny, but it is true. If you don't believe me, please review these topics in Books Online:

Transact-SQL Syntax Conventions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms177563.aspx
(See the second-last row in the first table.)

Deprecated Database Engine Features in SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms143729.aspx
(It's a lot of scrolling; search the page for "semicolon.")

I'm not sure if they're doing this because the ANSI standard now declares statement terminators as mandatory, or because as the T-SQL language gets more and more complex, the complexities in the parser code multiply, or a combination of both. But I am no longer on the fence: it is on my "long-term task list" to systematically attack chunks of T-SQL modules and fix these pesky situations where statement terminators are missing.

Conclusion

I've outlined several good reasons to get into the habit of ending all of your T-SQL statements with semi-colons, at least for work going forward. And I may have even painted a picture that shows you it may worthwhile to go back and "fix" your existing codebase -- maybe coupled with a simultaneous code review of some other kind, to help justify the effort. Because "a future version of SQL Server" might be closer than you think.

[Hmm, now I'm thinking the title of this post sounds a little dirty.]

Search results matching tags 'Best Practices' and 'future-proofing'

Bad habits to kick : ORDER BY ordinal

Ladies and gentlemen, start your semi-colons!