Column Aliases In ORDER BY Claus Cannot Be Prefixed By Table Alias – SQL 2008 R2

When running the SQL Upgrade Advisor on a database I’m migrating from SQL Server 2005 to Server 2008 R2, I get this error relating to a stored procedure:

Column aliases in ORDER BY clause cannot be prefixed by table alias

This means that your stored procedure has an alias used in a SELECT statement to identify a column, then later that alias is used in an ORDER BY clause. SQL Server 2005 allows this, but SQL Server 2008 R2 does not. Here’s an example, where the alias is used in an ORDER BY clause:

SELECT FirstColumn AS c1, SecondColumn AS c2
FROM db1.table1 t1
ORDER BY t1.c1

In the example, Server 2008 R2 will error because you aren’t allowed to use the column alias c1 in an ORDER BY clause, as the Upgrade Advisory message suggests.

To fix the error, remove the column aliases in the ORDER BY clause and replace them with the column name, as in the example below:

SELECT FirstColumn AS c1, SecondColumn AS c2
FROM db1.table1 t1
ORDER BY t1.FirstColumn

An alternative fix is to not prefix the table alias, as in the example below:

SELECT FirstColumn AS c1, SecondColumn AS c2
FROM db1.table1 t1
ORDER BY c1