SQL SERVER – Column Alias and Usage in Where Clause

You can assign a new name for a derived column in the SELECT statement which is also known as an alias name. But you need to understand how column alias are assigned and its scope

Let us create the following dataset

CREATE TABLE #temp (prod_id INT, transaction_Date DATETIME, amount DECIMAL(12,2))
INSERT INTO #temp
SELECT 1,'2010-10-10',2700.00 UNION ALL
SELECT 2,'2010-12-07',340 UNION ALL
SELECT 3,'2011-09-03',1200.50 UNION ALL
SELECT 2,'2011-08-11',4100 UNION ALL
SELECT 1,'2012-01-01',17650.50


Suppose you want to find the year of the transaction date and display in the SELECT statement and also order by itself. You can use

SELECT YEAR(transaction_date) AS YEAR,amount FROM #temp
ORDER BY YEAR

Which gives you the following result

year        amount
----------- --------------
2010        2700.00
2010        340.00
2011        4100.00
2011        1200.50
2012        17650.50

But what happens when you run the following?

SELECT YEAR(transaction_date) AS YEAR,amount FROM #temp
WHERE YEAR=2010

You get an error

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘year’.

It is because the column alias are not immediately known to the WHERE clause, whereas it is known in the ORDER BY clause because ORDER BY is executed lastly after the entire column list is known.

For more information about the order of execution in the statement, refer this my earlier blog.