SQL Server Execute Order

Question

As I know the order of execute in SQL is

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

So I am confused with the correlated query like the below code.

Is FROM WHERE clause in outer query executed first or SELECT in inner query executed first? Can anyone give me idea and explanation? Thanks

SELECT 
    *, COUNT(1) OVER(PARTITION BY A) pt  
FROM
    (SELECT 
         tt.*,
         (SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS A
     FROM  
         t tt
     WHERE  
         data > 10) t1

Check actual execution plan. Probably there will be Nested Loops — Lukasz Szozda, Aug 24 '18 at 16:26
check this: https://stackoverflow.com/questions/4596467/order-of-execution-of-the-sql-query — CR241, Aug 24 '18 at 17:44
Curious about this question... what do you hope to gain by expecting a certain order of operations? To my understanding, SQL Server will re-arrange things into an order if deems most efficient, but that can change with an identical query if the data and stats change. — UnhandledExcepSean, Aug 24 '18 at 18:36

score 2 · Answer 1 · answered Aug 24 '18 at 16:28

As I know the order of execute in SQL is FROM-> WHERE-> GROUP BY-> HAVING -> SELECT ->ORDER BY

False. False. False. Presumably what you are referring to is this part of the documentation:

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps.

As the documentation explains, this refers to the scoping rules when a query is parsed. It has nothing to do with the execution order. SQL Server -- as with almost any database -- reserves the ability to rearrange the query however it likes for processing.

In fact, the execution plan is really a directed acyclic graph (DAG), whose components generally do not have a 1-1 relationship with the clauses in a query. SQL Server is free to execute your query in whatever way it decides is best, so long as it produces the result set that you have described.

thanks fro responding. So for my case, what do you think the processing order? If my inner query where statement refers to the outer query row id? — Aiden, Aug 24 '18 at 16:32

SQL Server Execute Order

1 Answers1