Logical Query Processing in SQL Server

processingLogical Query Processing in SQL Server

I am active online community member and try to help more and more people via my blog and some other forums like Experts-Exchange.com (EE), Asp.net, MSDN etc. in my leisure time. I have recently seen one question in EE where one guy was asking that column alias is not working in his WHERE clause. Obviously, column alias of SELECT clause won’t work in WHERE clause as well as in JOIN clause due to execution order. SQL Server has its own way to execute your SELECT query and if you don’t know how it works, you may fall into this kind of issue. After seeing that question in EE, I decided to write this post.

You SELECT query might consists of SELECT, WHERE, JOIN, GROUP BY etc. clauses in it but SQL Server follows the following order to execute your query.

FROM
ON
OUTER
WHERE
GROUP BY
ROLLUP | CUBE
HAVING
SELECT
DISTINCT
ORDER BY
TOP

If you can see “WHERE” clause in in above list then you can find it at 4th position whereas SQL Server know about the column alias you have used in 4th from last position. This is the reason that you can’t get column alias in WHERE clause but you do get it in ORDER BY clause. Let us see it practically by executing simple SELECT query in AdventureWorks2012 database.


USE AdventureWorks2012

GO

SELECT

SalesOrderID AS SOID

,SalesOrderDetailID AS SODID

,CarrierTrackingNumber

,OrderQty

FROM

sales.SalesOrderDetail

WHERE

SOID=43659

--you will be greeted with error something like this:
--Msg 207, Level 16, State 1, Line 2
--Invalid column name 'SOID'.

Following query would work as we have proper column name in WHERE clause whereas alias in ORDER BY clause.

USE AdventureWorks2012
GO

SELECT
SalesOrderID AS SOID
,SalesOrderDetailID AS SODID
,CarrierTrackingNumber
,OrderQty
FROM
sales.SalesOrderDetail
WHERE
SalesOrderID=43659
ORDER BY SOID

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

One thought on “Logical Query Processing in SQL Server

Comments are closed.