What are the Phases of the Query Processing Logic in the SQL Server

SQL Query Processing Phases In the SQL Server
The Query processing logic in SQL is different from the other Programming languages, where the code is processed in the order in which it is written (called its typed order).

In SQL Server, the Logical processing order is different from typed order, the first clause that is processed is the FROM clause, where as the SELECT clause is almost processed later though it appears in the first in a query.

As we know that the SQL, was earlier known as the "SEQUEL" , in this original name the letter E stands for English. The designers of this language had in mind a declarative language where you declare your instructions in a language similar to English.

Consider the manner in which you provide an instruction in English "Bring me the T-SQL Querying Book from the Microsoft Store". Here, you indicate the Object(the Book) before indicating the location (the Store) from which you need to get the book. 

But it clearly indicating that the person carrying out the instruction would have to first go to the Store, then from there he obtain the Book.

In a similar manner, in SQL, the typed order indicates the SELECT clause with the desired columns before the FROM clause.

Each phase in Logical query processing generates a Virtual Table(VT) that is used as the input to the next step. These VTs are not available to the caller(client application or outer query). Only the table generated by the final step is returned to the caller. If a certain clause is not specified in the query, that step is simply skipped.

Logical Query processing Phases :
The following diagram showing the Logical Query processing in SQL Server.

                                                               Image courtesy : Microsoft

The following showing the Logical order numbers of the Query processing in SQL Server.

(5)    SELECT

(7)    TOP(<top_specification>)

         (5-2) DISTINCT

         (5-1) <select_list>

(1)    FROM 

         (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>

         (1-A) <left_table> <apply_type> APPLY <right_input_table> AS <alias>

         (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>

         (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alisas>

(2)    WHERE <where_predicate>

(3)    GROUP BY <group_by_specification>

(4)    HAVING <having_predicate>

(6)    ORDER BY <order_by_list>

(7)    OFFSET <offset_specification> ROWS FETCH NEXT <fetch_spec> ROWS ONLY;

Now we will discuss in detail about each Phase of the Query processing.

(1) FROM:

This Phase identifies the queries source tables and process the tables operators(eg. Pivot).

Each table operator applies a series of sub phases. For example, the phase involved in a Join are (1-J1) Cartesian Production, (1-J2) ON Predicate, (1-J3) Add Outer Rows. These phases generates a first virtual table VT1.

(1-J1) Cartesian Product : 

This phase performs a Cartesian Product(Cross Join) between the two tables involved in the table operator, generating VT1-J1.

(1-J2) ON Predicate :

This phase filters the rows from VT1-J1 based on the predicate that appears in the ON Clause(<on_predicate>). Only the Rows for which the predicate evaluates to True are inserted in to VT1-J2.

(1-J3) Add Outer Rows :

If OUTER Join is specified(as opposed to CROSS Join or INNER Join), rows from the preserved table or tables for which a match was found are added to the rows from VT1-J2 as outer rows and then generates VT1-J3.

(2) WHERE :

This phase filters the rows from VT1 based on the predicate that appears in the Where clause (<where_predicate>). Only the rows for which the predicate evaluates to True are inserted into VT2.

(3) GROUP BY :

This phase arranges the rows from VT2 into groups based on the set of expressions (aka, grouping set) specified in the GROUP BY Clause, and generating VT3. Ultimately there will be one result row per qualifying group.

(4) HAVING :

This phase filters the groups from VT3 based on the predicate that appears in the Having clause (<having_predicate>). Only groups for which the predicate evaluates to True are inserted into VT4.

(5) SELECT :

This phase processes the elements in the SELECT clause and then generates VT5.

(5-1) Evaluate Expressions :

This phase evaluates the expressions in the SELECT list, generates VT5-1.

(5-2) DISTINCT :

This phase removes the duplicates from VT5-1 and generates VT5-2.

(6) ORDER BY :

This phase orders the rows from VT5-2 according to the list specified in the Order By clause and generates the Cursor VC6.(A Cursor is a temporary work area created in the system memory when a SQL statement is executed. A Cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data.)

In case of Absent of an Order BY clause then VT5-2 will becomes VT6.

(7) TOP | OFFSET-FETCH :

This phase filters the rows from VC6 or VT6 based on the Top or Offset-Fetch specification and the then generates VC7 or VT7 respectively. With Top, this phase filters the specified no.of rows based on the ordering in the Order By clause, or based on the arbitrary order when Order By clause is absent.

With Offset-Fetch, this phase skips the specified no.of rows and then filters the specified no.of rows based o n the ordering in the Order By clause. The Offset-Fetch filter was introduced in the SQL Server-2012.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------