SQL: Don't use numeric constants in T-SQL ORDER BY clauses

In the T-SQL language you can specify a positive integer constant in an ORDER BY clause as I’ve shown in the main image above.

Please don’t do this !

For those that haven’t used this, the number refers to the position of the column in the SELECT list. If you do this, you are creating really fragile code and also code that’s really hard to read, particularly as the statements get more complex.

I know that you might have an expression without an alias, and it might seem painful to repeat it in the ORDER BY clause but please do that anyway. Or better still, add an alias to the expression.

If you do have a column alias, you can use it in an ORDER BY clause.

In the past, some SQL engines (not SQL Server) haven’t allowed you to use column aliases in an ORDER BY but SQL Server does. In fact, they all should. The clauses in a SQL query are logically performed in this order:

FROM WHERE GROUP BY HAVING SELECT ORDER BY

So you can see that while you can’t use a column alias in a WHERE clause, you can use one in an ORDER BY clause.

Other database engines (like Snowflake -> I’m looking at you), also allow numeric constants in a GROUP BY clause. If you use one of those database engines, please don’t do that either, and for exactly the same reasons.

2019-06-27