Statement evaluation precedence

Question

Statement evaluation precedence

Tom257

SSChampion

Points: 11543
More actions
May 6, 2010 at 8:16 pm

#232386

Comments posted to this topic are about the item Statement evaluation precedence
May 7, 2010 at 2:14 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

This is a very good question, thank you Tom. Today I am the lucky one to be first who answered it correctly. It was not too difficult to deduce correct answer because it was the only one noticeably making perfect sense. The Logical Query Processing Diagram is great, I will definitely print it out and have it posted on the wall by my desk.

Oleg

Paul White SSC Guru Points: 150467 More actions · Answer 2

Yay! Another point. Found this one quite easy - but only because I happened to re-read Itzik Ben-Gan's Inside Microsoft SQL Server 2008: T-SQL Querying recently. (Chapter 1)

Itzik describes the logical order of processing as:

FROM

Cartesian Product

ON clause filter

Add Outer Rows

WHERE

GROUP BY

HAVING

SELECT

Evaluate expressions

DISTINCT

TOP

Presentation ORDER BY

I would just add that the physical order of operations performed may differ from the logical order due to query optimisation.

Well done Tom.

edit: added further details after re-reading the question explanation

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

woot - got it correct.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Though I got this correct, it appears there is a typo in the correct answer. My version had the last two flip-flopped based on the source document provided in the answer.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 5

CirquedeSQLeil (5/6/2010)
Though I got this correct, it appears there is a typo in the correct answer. My version had the last two flip-flopped based on the source document provided in the answer.

What are you referring to here Jason?

It must be hard to spot - I can't see anything wrong...:unsure:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 6

sharath.chalamgari

SSCertifiable

Points: 5680

May 7, 2010 at 12:25 am

#1163822

Good Question Tom:-)

Tom257 SSChampion Points: 11543 More actions · Answer 7

Thank-you all.

I've had this data for 10 years now, maybe I copied it from someone's SQL 2000 book - or possibly it came from an SQL 7 course I did (in 1999!), and I found it really useful, But I was slightly unsure that it was still valid, particularly as I had some difficulty in finding a concrete reference.

Thanks Paul for the tip on Itzik's book - I'll be visiting Amazon soon I think (or should I wait for the R2 edition?)

ziangij SSCrazy Eights Points: 8066 More actions · Answer 8

ziangij

SSCrazy Eights

Points: 8066

May 7, 2010 at 2:12 am

#1163862

got to learn something new today... thanks 🙂

Sue Boyington SSC Eights! Points: 850 More actions · Answer 9

There is another link on this topic that may be of interest:

http://www.bennadel.com/blog/70-SQL-Query-Order-of-Operations.htm

deepak.a SSCertifiable Points: 5330 More actions · Answer 10

deepak.a

SSCertifiable

Points: 5330

May 7, 2010 at 3:37 am

#1163899

Thanks For the Good Question 🙂

Rob Ashton Right there with Babe Points: 785 More actions · Answer 11

An excellent question, one which should etched in DB developers' heads IMO. All too often I've heard folks complain that their query plan isn't what they wanted and blamed SQL Server for getting it wrong when actually they've not understood the processes that go on once they hit Execute...

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 12

I have to disagree with the comments so far. I do not consider this a very good question.

The processing order as described in the correct answer is the logical order of evaluation. What order a Relational Database Management System uses internally is completely implementation dependant; any internal processing order is valid as long as the results are the same as when the logical processing order had been used. This is where the query optimizer comes in - it considers countless various processing orders and access methods to find the one with the lowest estimated cost.

I would have liked the question (a lot!) if the question had asked for the logical processing order. But the question specificallly asked aboout the sequence internally used, and that threw me off completely. I did answer correctly, but only because, after reading the answer options, I was able to work out what the intention of the question was.

Thanks for taking the effort to submit a question, Tom. I know how much work goes into it, and I appreciate the effort. I hope my harsh comments won't keep you from submitting more questions.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Tom257 SSChampion Points: 11543 More actions · Answer 13

Hugo Kornelis (5/7/2010)
I have to disagree with the comments so far. I do not consider this a very good question.
The processing order as described in the correct answer is the logical order of evaluation. What order a Relational Database Management System uses internally is completely implementation dependant; any internal processing order is valid as long as the results are the same as when the logical processing order had been used. This is where the query optimizer comes in - it considers countless various processing orders and access methods to find the one with the lowest estimated cost.
I would have liked the question (a lot!) if the question had asked for the logical processing order. But the question specificallly asked aboout the sequence internally used, and that threw me off completely. I did answer correctly, but only because, after reading the answer options, I was able to work out what the intention of the question was.
Thanks for taking the effort to submit a question, Tom. I know how much work goes into it, and I appreciate the effort. I hope my harsh comments won't keep you from submitting more questions.

I attach my test script, where I was attempting to confirm the validity of my 10-year-old slip of paper, by looking at actual execution plans, and matching up the parts of T-SQL to the plan.

I avoided using 'logical' because the execution plan seemed to back up the sequence of evaluation. The plans show data being moved from right to left, joined, filtered, sorted, etc. This is my explanation for the use of internal sequence of evaluation.

Perhaps these plans only represent the logical way a query is handled, and don't really reflect the true sequence of processing? If so then can we ever truely know whats going on.

USE TEMPDB;

GO

CREATE TABLE Items (

pKey INT NOT NULL,

Created DateTime,

ID char(20),

Region INT,

fItemType INT );

CREATE TABLE ItemTypes (

pKey INT NOT NULL,

Description varchar(50),

CODE char(20) );

INSERT INTO ItemTypes

SELECT 1, 'Purchase Order', 'ORDER' UNION ALL

SELECT 2, 'Invoice Order', 'INVOICE';

INSERT INTO Items

SELECT 1, GetDate(), '381203', 1, 1 UNION ALL

SELECT 7, DATEADD(day,-1,GetDate()), '371203', 3, 1 UNION ALL

SELECT 8, DATEADD(day,-1,GetDate()), '371203', 3, 1 UNION ALL

SELECT 2, DATEADD(day,-2,GetDate()), '391203', 1, 1 UNION ALL

SELECT 3, DATEADD(hour,-1,GetDate()), '383203', 2, 2 UNION ALL

SELECT 4, DATEADD(day,-1,GetDate()), '385203', 2, 2 UNION ALL

SELECT 5, DATEADD(year,-1,GetDate()), '394203', 3, 2 UNION ALL

SELECT 6, DATEADD(month,-1,GetDate()), '340203', 3, 2 ;

-- Test Query - get the actual execution plan for this

-- remember to read from right to left

SELECT DISTINCT TOP(3) I.ID, COUNT(I.Region) AS RCount, T.Description, I.Created, T.CODE

FROM dbo.Items AS I

JOIN dbo.ItemTypes AS T ON I.fItemType = T.pKey

WHERE I.Created > '2009-12-10'

AND T.Description like '%Order'

GROUP BY T.CODE, I.ID, I.Region, T.Description, I.Created, T.CODE

HAVING COUNT(I.Region) <= 1

ORDER BY I.Created DESC, T.CODE

ALTER TABLE dbo.Items

ADD PRIMARY KEY CLUSTERED (pKey) ;

ALTER TABLE dbo.ItemTypes

ADD PRIMARY KEY CLUSTERED (pKey);

ALTER TABLE dbo.Items

WITH CHECK ADD FOREIGN KEY (fItemType) REFERENCES dbo.ItemTypes(pKey);

-- try again with clustered & foreign keys (doesn't make a difference to the processing order