jump to navigation

Looking into logical query processing in SQL Server August 7, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
trackback

A few weeks back, I received an email from a good colleague of mine that was really puzzled with the execution of a rather simple query.Finally he gave up and draw the conclusion that there was some sort of a bug in SQL Server.I tried this query myself and it did not work.The query was pretty simple and I could not see why it was not working,in the first place.The answer to this “strange” behavior of  SQL Server is not strange at all. We write SQL queries using the T-SQL syntax.The way we form a SQL query is not the same way SQL Server logically (internally) executes the query.

Let’s see that with a hands-on example.

I am using the AdventureWorks2012 database.Make sure you visit the CodePlex website and download it.Then attach it (restore it) to your local SQL Server instance.

I will be using SQL Server 2012 Developer Edition but the Express edition will suffice .

I connect to the SQL Server instance.

Type (copy and paste the code below) the code below in a new query window in SSMS. Make sure you review it. At first glance there is nothing wrong.Try to execute the query.


USE AdventureWorks2012
GO

SELECT  [JobTitle] ,
[BirthDate] ,
[MaritalStatus] ,
[Gender] ,
[HireDate] ,
[VacationHours] ,
[SickLeaveHours] ,
( [VacationHours] + [SickLeaveHours] ) AS [TotalLeaveHours]
FROM    [AdventureWorks2012].[HumanResources].[Employee]
WHERE   [TotalLeaveHours] >=70

You will receive an error.

Invalid column name ‘TotalLeaveHours’.

SQL Server executes internally the SQL query with a different logic. It does not follow the order of the statements as they are depicted in the SQL Query.The order of execution is the following

  • FROM
  • ON
  • JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • DISTINCT
  • ORDER BY
  • TOP

You can see now why my query failed. SELECT is evaluated after the WHERE clause.

One way to visualize what I am explaining, please have  a look (download) this poster.

Let’s rewrite the previous statement.

Execute the code below


USE AdventureWorks2012
GO

SELECT  [JobTitle] ,
[BirthDate] ,
[MaritalStatus] ,
[Gender] ,
[HireDate] ,
[VacationHours] ,
[SickLeaveHours] ,
( [VacationHours] + [SickLeaveHours] ) AS [TotalLeaveHours]
FROM    [AdventureWorks2012].[HumanResources].[Employee]
ORDER BY [TotalLeaveHours] DESC

You can see now why my query succeeds. ORDER BY is evaluated after the SELECT clause.

Another excellent article on the subject is this one (greek audience only :P).

I am big fan of LINQ to SQL and Entity Framework. I am sure you have written at least one query using the LINQ syntax.Have a look at the ( snippet )code below

var companies = from comp in ctx.Companies
                                   where comp.CompanyName.StartsWith(“Ad”)
                                   orderby comp.CompanyName
                                   select new { comp.CompanyID, comp.CompanyName };

I am sure you can see the similarities between LINQ query syntax and how SQL Server processes a query internally. So if you think that LINQ syntax is “strange” you know now why we write the LINQ queries the way we do.
Hope it helps!!!

Comments»

1. Dot Net Rules : Looking into logical query processing in SQL Server - August 7, 2012

[…] way we form a SQL query is not the same way SQL Server logically (internally) executes the query. (read more) Share Posted: Τρίτη, 7 Αυγούστου 2012 3:57 μμ από το μέλος […]

2. ckotsidimos - August 8, 2012

I was playing with the query and found out that it is working ok when using the following

USE AdventureWorks2012
GO

SELECT [JobTitle] ,
[BirthDate] ,
[MaritalStatus] ,
[Gender] ,
[HireDate] ,
[VacationHours] ,
[SickLeaveHours] ,
( [VacationHours] + [SickLeaveHours] ) AS [TotalLeaveHours]
FROM [AdventureWorks2012].[HumanResources].[Employee]
WHERE ( [VacationHours] + [SickLeaveHours] ) >=70

as you can see I put the Function in the where clause so there was no need to use the alias [TotalLeaveHours]

This further strengthens what you say! Can you confirm?

fofo - August 8, 2012

yes, that works !!!there is no problem with the query logical processing

3. Dimitris Staikos - September 6, 2012

I would probably find that amusing 20 years ago. It was about that time that I decided to follow a simple rule: Write code in such a way so that I don’t have to look up or even remember any special rules about precedence, left-to-right or right-to-left order of evaluation etc. If the code leaves no doubts even to a *novice* reading it reading it for the first time, then it is good code.

Btw, I recently found a bug in SQL2008 R2 query processor, reproduced and confirmed by MS support in Germany. You should have seen the river of explanations that I got from various people trying to explain to me that the query processor works in strange and unexpected ways (which they didn’t know either, they were just parroting) and trying convince me that what I was getting was the correct behavior etc.
It’s a favorite sport for many to mess around with “under the hood” stuff like the query processor and the execution plans and the plan cache, etc, but this is deep stuff and so far I haven’t met anyone who really knows what he is talking about. That will probably happen when I meet with an SQL Server escalation engineer :-)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: