Statement evaluation precedence

  • Comments posted to this topic are about the item Statement evaluation precedence

  • 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

  • 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

  • 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

  • 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

  • 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:

  • Good Question Tom:-)

  • 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?)

  • got to learn something new today... thanks 🙂

  • This was removed by the editor as SPAM

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

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

  • Thanks For the Good Question 🙂

  • 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...

  • 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/

  • 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

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

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