Column Alias and CTE

By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

April 1, 2009

One of the most annoying limitations of T-SQL is the restriction of column alias to be only used in the ORDER BY clause. It cannot be reused in SELECT, cannot be used in WHERE or GROUP BY. Take a look at the following example:

USE AdventureWorks2008;

GO

SELECT   YEAR = year(OrderDate),

         MONTH = month(OrderDate),

         OrderTotal = sum(LineTotal)

FROM     Sales.SalesOrderHeader h

         JOIN Sales.SalesOrderDetail d

           ON h.SalesOrderID = d.SalesOrderID

GROUP BY year(OrderDate),

         month(OrderDate)

ORDER BY YEAR DESC,

         MONTH DESC

GO

/* Partial results

YEAR  MONTH OrderTotal

2004  7     50840.630000

2004  6     5364840.179338

2004  5     5194121.522904

*/

Column alias "Year" cannot be used in the group by but in the order by. More than just annoyance: more complicated, harder-to-maintain code with potential source for bugs.

In SQL 2000 we could have tried to use derived tables to introduce a structure to isolate the detail info from the group by:

SELECT   YEAR,

         MONTH,

         OrderTotal = sum(OrderAmount)

FROM     (SELECT YEAR = year(OrderDate),

                 MONTH = month(OrderDate),

                 OrderAmount = LineTotal

          FROM   Sales.SalesOrderHeader h

                 JOIN Sales.SalesOrderDetail d

                   ON h.SalesOrderID = d.SalesOrderID) a

GROUP BY YEAR,

         MONTH

ORDER BY YEAR DESC,

         MONTH DESC

It looks more controlled, but does not appear to be simpler. Derived tables in SQL2K actually belonged to the domain of expert database coders, casual database developers were terrified by the seemingly illogical construct.

In SQL Server 2005 and SQL Server 2008, we can magically change the derived table into a CTE:

WITH cteOrderDetail

     AS (SELECT YEAR = year(OrderDate),

                MONTH = month(OrderDate),

                OrderAmount = LineTotal

         FROM   Sales.SalesOrderHeader h

                JOIN Sales.SalesOrderDetail d

                  ON h.SalesOrderID = d.SalesOrderID)

SELECT   YEAR,

         MONTH,

         OrderTotal = sum(OrderAmount)

FROM     cteOrderDetail

GROUP BY YEAR,

         MONTH

ORDER BY YEAR DESC,

         MONTH DESC

Indeed, this is the simplest structure. Simple but powerful. This is why CTEs have been declared the vehicle to increase SQL development productivity. Simpler structure is easier to create and maintain. The Year and Month column aliases declared only at one place and used in three places: SELECT, GROUP BY and ORDER BY.