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 .....*/
