SQL Server – Logical Binding Order or Logical Query Processing Phases

Today I am going to talk about the logical binding order.  This question has been asked to me many times. Here I will try to explain the things in simple manner. Basically SQL is a Declarative language and is not fully relational. It is different from other languages like C# or VB where you have a direct relationship between what you write and how the program works internally, But here the case is different. First go through the Logical Binding Order or Logical Query Processing Phases below-

• FROM
• ON
• JOIN
• WHERE
• GROUP BY
• CUBE / ROLLUP
• HAVING
• SELECT
• DISTINCT
• ORDER BY
• TOP
  

Also understand that this is different from how the things are executed physically or physical execution of the query. Optimizer may change your query internally to fetch the results in an effective manner. E.g. Push down the predicate to the leaf or rewrite the sub query using joins, etc. SQL Engine works row by row mode like what we have in Cursor. Basically, Data flows from RIGHT (TOP) to LEFT in the query plan. That is it data centric view of the query plan. Logic in the query plan flows from LEFT to RIGHT. This is logic centric view of the query plan.

All the operators used in the execution plan are called physical operators or Iterators. Let’s go through an example below-

We know that select and top operator cannot have rows from their own. They are dependent on Clustered Index scan in this case. So when the execution starts the “Select” operator asks the “Top” operator that hey do you have any rows for me, Top operator says that I don’t have rows let me ask “Clustered Index Scan”. Hey “Clustered Index scan” do you have any rows for me and “Clustered index scan” says yes I do have row for you. This goes on till the parent consumer iterator asks for rows.

That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with more articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com