Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Statement evaluation precedence


Statement evaluation precedence

Author
Message
Sue Boyington
Sue Boyington
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 179
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
deepak.a
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 863
Thanks For the Good Question Smile
Rob Ashton
Rob Ashton
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 133
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
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11554
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 1466
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
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


-- clean up
DROP TABLE TempDb.dbo.Items
DROP TABLE TempDB.dbo.ItemTypes



Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Tom Brown (5/7/2010)
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?)

AFAIK he's not releasing an R2 update. It is an excellent book - very dense, very technical in places, but if you love that sort of thing...you'll love it :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Hugo Kornelis (5/7/2010)
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.

Hmmm it seems my brain substituted the word 'internally' for 'logically' without conscious intervention - I had to go back and re-read the question to see that you are right about this. You have a good point.

I still think it's a great question though :-P



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11554
Tom Brown (5/7/2010)
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.


Hi Tom,

The execution plan represents the actual processing order, you looked at the right thing. But you made two mistakes. One is not looking good enough, the other one is assuming that, if it holds for this example, it always holds.

Run the example again. Check the execution plans. This time, hover your mouse over any of the two table scan operators in the first query, or any of the two clustered index scan operators in the second query. Check what is listed as "Predicate". You will see that the WHERE clause has been pushed up; the Query Optimizer has decided to filter rows based on the WHERE clause because that reduces the query cost without affecting the results.

Of course, there might well be situations where the actual internally used order matches the logical processing order exactly. If there are no cheaper alternatives, that is what SQL Server will do. My point is that this will not always happen (and, in fact, almost never happens).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 1466
Thanks Hugo.

I'd never noticed those 'Predicate' parts of the plan before. Just goes to show, no matter how closely you investigate something, there is always more to learn.
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 996
The key for me getting this right was starting at the end. I knew it had to end DISTINCT, ORDER BY, COUNT -- fortunately there was only one answer with that ending! :-)

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search