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.

[sourcecode language=”sql”]
FROM
ON
OUTER
WHERE
GROUP BY
ROLLUP | CUBE
HAVING
SELECT
DISTINCT
ORDER BY
TOP
[/sourcecode]

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.

[sourcecode language=”sql”]

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’.
[/sourcecode]

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

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

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

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.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Logical Query Processing in SQL Server”

Comments are closed.