THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

Logical Query Processing

 

When I first began working with SQL Server several years ago, one thing that really hung me up was, not understanding why I could reference an Alias column in certain parts of my query, but not in all parts of my query. To be more specific, why I could reference my Alias column in my Order By clause, but I couldn’t reference the alias in my Group By, Having, or Where clauses. I never fully understood why this was so until I read about Logical Query Processing in “Microsoft SQL Server 2008 T-SQL Fundamentals” written by Itzik Ben-Gan.

To illustrate the issue, when we run the following query against the AdventureWorks2012 database, referencing an Alias name in the Order By clause poses no issue for SQL Server and the data is returned.

SELECT OrganizationLevel, YEAR(BirthDate) YearOfBirth, MONTH(HireDate) MonthOfHire, sum(VacationHours) TotalVacationHours

FROM AdventureWorks2012.HumanResources.Employee

WHERE OrganizationLevel = 4 --and YearOfBirth = 1985

GROUP BY OrganizationLevel, Year(BirthDate), Month(HireDate), VacationHours

HAVING VacationHours > 50

ORDER BY YearOfBirth DESC, MonthOfHire DESC

clip_image002

However, if we uncomment the Alias reference in the WHERE clause, SQL Server will return an error

SELECT OrganizationLevel, YEAR(BirthDate) YearOfBirth, MONTH(HireDate) MonthOfHire, sum(VacationHours) TotalVacationHours

FROM AdventureWorks2012.HumanResources.Employee

WHERE OrganizationLevel = 4 and YearOfBirth = 1985

GROUP BY OrganizationLevel, Year(BirthDate), Month(HireDate), VacationHours

HAVING VacationHours > 50

ORDER BY YearOfBirth DESC, MonthOfHire DESC

image

So what exactly is the reason for this error? Well the answer comes down to the way that the query is broken down and processed by SQL Server. The order of query processing in SQL Server is:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

Using the above order of processing, our sample query first pulled out all of the data FROM the AdventureWorks2012.HumanResources.Employee table and filters for only data WHERE OrganizationalLevel = 4. Next the query GROUP’s the result set BY OrganizationalLevel, Year(BirthDate), Month(HireDate), and VacationHours. Next the query performs another filter on this group set for anything HAVING VacationHours > 50. Next the query SELECT’s the specified columns from the SELECT list and assigns the Alias names at this point. Lastly the query ORDER’s the result set BY your specified criteria, and then returns the data to the requesting client.

As we see, the alias names were not created until Step 5 (SELECT). As a result, this explains why we are able to reference the Alias names in Step 6 (ORDER BY), but not in Step 2 (WHERE), Step 3 (GROUP BY), or Step 4 (HAVING).

Understanding how Logical Query Processing works will help you write better and more efficient queries. For more information on the topic, I highly recommend reading “Microsoft SQL Server 2008 T-SQL Fundamentals” written by Itzik Ben-Gan.

Published Friday, May 31, 2013 4:54 PM by Tamarick Hill
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement