SQL execution Order of T-SQL

The most obvious feature of SQL differs from other programming languages is the order in which the code is processed. In a large number programming language, code is processed in encoded order, but in the SQL language, the first processed clause is the FROM clause, although the SELECT statement first appears, but is almost always finally processed.

Each step produces a virtual table that is used as input to the next step. These virtual tables are not available to callers (client applications or external queries). Only the table generated in the last step is returned to the caller. If you do not specify a clause in the query, the corresponding step is skipped.

First, a simple description of each logical step used for SQL Server 2000 and SQL Server 2005:

(8)SELECT(9)DISTINCT( One)<Top_specification> <Select_list>(1) from <Left_table>(3)<Join_type> JOIN <Right_table>(2) on <Join_condition>(4)WHERE <Where_condition>(5)GROUP  by <Group_by_list>(6) with{CUBE|ROLLUP} (7) having <Having_condition>(Ten)ORDER  by <Order_by_list>

Introduction to the Logical query processing phase

1. from: performs a cartesian product (Cartesian product) (cross join) of the first two tables in the FROM clause, generating the virtual table VT1.
2. on: applies the on filter to the VT1. Only those lines that make <join_condition> true are inserted into the VT2.
3. OUTER (join): If you specify a OUTER join (relative to a cross join or (INNER join), keep the table (preserved Table: Left outer join marks the left table as a reserved table, right outer join marks the right table as a reserved table, a full outer join marks two tables as a reserved table), the matching rows are not found in the row as outer rows are added to VT2, generating VT3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table and the next table that you generated for the previous join until you have finished processing all the tables.
4. Where: applies a where filter to VT3. Only rows that make <where_condition> true are inserted into the VT4.
5. GROUP by: groups The rows in the VT4 by the list of columns in the GROUP BY clause, generating VT5.
6. cube| ROLLUP: Inserts a Hyper-group (suppergroups) into VT5, generating VT6.
7. Having: applies a having filter to VT6. Only groups that make
8. Select: processes the select list, producing VT8.
9. DISTINCT: removes duplicate rows from VT8, resulting in VT9.
10. ORDER by: to generate a cursor (VC10) by sorting the rows in VT9 by the list of columns in the ORDER by clause.
11. TOP: selects the specified number or scale of rows from the beginning of VC10, generates the table VT11, and returns the caller.

　　Note: step 10, sort the rows returned by the column list in the ORDER BY clause, and return the cursor VC10. This step is the first and only step to use the column aliases in the select list. This step differs from the other step in that it does not return a valid table, but instead returns a cursor. SQL is based on the set theory. The collection does not pre-order its rows, it is only a logical collection of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a proper understanding of SQL.

　　because this step does not return a table (instead of returning a cursor), a query that uses the ORDER BY clause cannot be used as a table expression. table expressions include: views, inline table-valued functions, subqueries, derived tables, and common expressions . Its result must be returned to the client application that expects to get the physical record.

For example, the following derived table query is invalid and produces an error:

SELECT * FROM (select Orderid,customerid from Orders order by OrderID) as D

The following view also generates an error

Create VIEW My_viewasselect *from Ordersorder by OrderID

　　In SQL, a query with an ORDER BY clause is not allowed in a table expression, but there is an exception in T-SQL (apply top option).

So remember, don't assume any particular order for the rows in the table. In other words, do not specify an ORDER BY clause unless you are sure you want to order rows. Sorting is a cost, and SQL Server needs to perform an ordered index scan or use the sort runner.

Second, in the SQL Server 2008 version, the description of the logical phase extends to all logical statements, not just join processing, such as apply, pivot, and so on. In this way, the logical steps are divided into 6 parts, and some of the steps include the sub-steps.

(5)SELECT(5-2)DISTINCT(5-3)<Top_specification>(5-1)<Select_list>(1) from(1-J<Left_table> <Join_type> JOIN <Right_table>  on <Join_condition>        |(1-A<Left_table> <Apply_type>APPLY<Right_table_expression>  as <Alias>        |(1-P<Left_table>PIVOT (<Pivot_specifications>) as <Alias>        |(1-U<Left_table>UNPIVOT (<Unpivot_specifications>) as <Alias>(2)WHERE <Where_condition>(3)GROUP  by <Group_by_list>(3-CR) with{CUBE|ROLLUP} (4) having <Having_condition>(6)ORDER  by <Order_by_list>

Describes the process of each process step in more detail:

　　

This step classification is more comprehensive and specific than the 2005 version. I added the 3-CR in the above steps, and I think the description is more comprehensive. The WITH rollup and with cube parameters in 3-CR are replaced by the grouping sets, rollup, and cube operators of the GROUP BY clause in SQL Server 2008 and are no longer recommended for use with non-ISO compliant ROLLUP, with Cube, and all syntax. However, this does not affect the order of logical processing.

The following is a description of the steps in the logical process, note the build steps for the virtual table (VTn):

　　1. From: This step is used to validate the source table of the query and to process the table operator. Each table operator is applied to a series of sub-steps. For example, the following sub-steps are involved in the (1-j) step above for joins. After these sub-steps are finalized, the virtual table VT1 is generated.

[1] (1-j1): perform left_table and right_table two table cross join (flute Descartes product), generate virtual table vt1-j1;

[2] (1-J2): on the flute Descartes product application on filter, generate virtual table vt1-j2;

[3] (1-J3): If an outer join, in this step will be filtered out of the external row to vt1-j2, generate VT1-J3. Otherwise, the step is skipped.

　　2. Where: apply a where filter to VT1 and insert rows that match the filter criteria into VT2.

　　3. GROUP BY: groups the rows in VT2 by the list of columns in the GROUP BY clause, generating VT3. If the statement contains a with cube or with ROLLUP, the grouping statistic results are added again after the total is inserted into the VT3, generating VT3-RC.

　　4. Having: apply a having filter to VT3 and insert rows that match the filter criteria into the VT4.

· Step 5 (SELECT): Process the elements in the SELECT clause to generate VT5.

U (5-1) calculation expression: This step calculates the expression in the select list and generates VT5-1;

U (5-2) DISTINCT: Remove duplicate rows from vt5-1, generate vt5-2;

U (5-3) TOP: This step filters the specified number or scale of rows from the beginning of Vt5-2, based on the collation specified in the ORDER BY clause.

· Step 6 (ORDER by): This step sorts the rows in vt5-3 by the list of columns in the ORDER by clause, generating a cursor VC6.

SQL execution Order of T-SQL