 Column Alias and CTE
By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, 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.
Related articles:
Column Alias – SQL Server Syntax Example: Column Alias – T-SQL Example
SQL Alias
The CTE, the hierarchical query and SQL Server 2005
|
