Execution sequence parsing of T-SQL query statements

(5) SELECT (5-2) DISTINCT (5-3) TOP (<top_specification>) (5-1) <select_list>

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

(2) WHERE <where_predicate>

(3) group by <group_by_specification>

(4) HAVING

(6) order by <order_by_list>;

A virtual table is generated for each step, which is used as the input for the next step. These virtual tables are not available to callers. Only the virtual tables generated in the last step are returned to the callers. If no clause is specified in the query, the corresponding steps are skipped. Next we will briefly describe these different logical steps.

(1)FROMThe FROM stage identifies the source table for query and processes table operators. Each table operator also applies a series of substages. For example, the stage involved in join operations is (1-J1) Cartesian Product, (1-J2) ON filter, and (1-J3) add external rows. The FROM stage generates the virtual table VT1.

(1-J1)Cartesian ProductIn this phase, the two tables involved in the table operator perform Cartesian Product (cross join) to generate a virtual table VT1-J1.

(1-J2)ON FilterThis phase filters the rows in the VT1-J1 Based ON the predicates that appear in the ON clause. The VT1-J2 can be inserted only for rows whose value is TRUE.

(1-J3)Add external rowIf outer join is specified (relative to cross join or inner join), no matching rows are found in the reserved table (preserved table) and added to the VT1-J2 as external rows to generate a VT1-J3.

(2)WHEREIn this phase, the rows in VT1 are filtered Based on the predicates (<where_predicate>) in the where clause. Only rows with the calculated result of the predicates being TRUE are inserted into VT2.

(3)GROUPGroups the rows in VT2 according to the column Name List specified in the group by clause to generate VT3.

(4)HAVINGFilters groups in VT3 Based on the predicates (

(5)SELECTProcess the elements in the SELECT clause to generate VT5.

(5-1)Calculation ExpressionCalculates the expression in the SELECT list to generate a VT5-1.

(5-2)DISTINCTRemoves duplicate rows from the VT5-1 and generates a VT5-2.

(5-3)TOPBased on the logical sorting defined by the ORDER BYi clause, from the VT5-2, select the rows that are specified in the previous count or percentage to generate the table VT5-3.

(6) order by sorts the rows in the VT5-3 according to the column Name List specified in the order by clause and generates the cursor VC6.

The above is a complete T-SQL statement execution process, hope to help everyone.

The above content from the Microsoft SQL Server 2008 technology insider T-SQL query book.