Column Alias and CTE

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. Though this is a standard SQL feature, it is very different from other application programming languages. The likelyhood is very tiny that alias handling will be streamlined in the near future at least.

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.

While on the topic of column aliases, string literals (within single quotes) can be used for column aliases as well.

SELECT ProductNumber, 'ProductName' = Name, StandardCost,

      'Color'=coalesce(Color, 'N/A')

FROM AdventureWorks2008.Production.Product

ORDER BY 'ProductName'

GO

/* ProductNumber  ProductName       StandardCost      Color

AR-5381     Adjustable Race         0.00              N/A

ST-1401     All-Purpose Bike Stand  59.466            N/A

CA-1098     AWC Logo Cap (XL)       6.9223            Multi

BE-2349     BB Ball Bearing         0.00              N/A

BA-8327     Bearing Ball            0.00              N/A   .....*/