SQL Server order of execution and query plan

Question

I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc):

FROM, JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

However, I cannot understand how in the query plan the sort (which corresponds to the order by) is done before the select (given that they are read from right to left) if the order of execution is per above => SELECT before ORDER BY.

An simple example is as follows:

enter image description here

I excluded a JOIN on purpose as to avoid the case of having that sort corresponding to the JOIN.

Answer 1

The order of execution you have quoted is a version of the Logical Processing Order of the SELECT statement - as the documentation states. The actual physical execution of the statement is determined by the query processor and the order may vary from this list. Also the SELECT node in the plan isn't an execution plan operator at all. This root node just labels the type of statement.

I.e., the DBMS is free to do it any way it wants, as long at it returns the same result as if it had used the logical execution order.

• Tibor Karaszi: I.e., the DBMS is free to do it any way it want, as long at it return the same result as if it would have used the logical execution order.
• Michael Green: @TiborKaraszi feel free to edit as you choose - it's how the site works.

Answer 2

As was already said, the logical processing of the SELECT statement and the order of operations to satisfy that statement are different.

As to the order of operations, you have to understand that you're looking at both an instantiation order and a data flow order when you look at an execution plan. The order it's laid out in, reading order in English, left to right, is the order in which the operators are instantiated. You can validate this by looking at the operator id value. For the plan above the Sort will be 1 and the Scan will be 2. However, the data flows from the scan into the the Sort. So the data is read from disk first through the Scan operation where it flows into the Sort, following the direction of the arrows in the plan.

The SELECT operator is technically not an operator (although, what else do we call it?). It represents a header for the plan. Look at it's Properties (not the tool tip) for a TON of really good information about the plan and it's behavior.