SQL语句执行顺序 in SQL Server 2005

1.     Logical Query Processing Phases

After reading Inside Microsoft SQL Server 2005 T-SQL Querying, take some notes.

This section introduces the phases involved in the logical processing of a query. Listing 1-1 contains a general form of a query, along with step numbers assigned according to the order in which the different clauses are logically processed.

(8)  SELECT (9)  DISTINCT (11) <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> (10) ORDER BY <order_by_list>

                                                               Listing 1-1. Logical query processing step numbers

The first noticeable aspect of SQL that is different than other programming languages is the order in which the code is processed. In most programming languages, the code is processed in the order in which it is written. In SQL, the first clause that is processed is the FROM clause, while the SELECT clause, which appears first, is processed almost last.

Each step generates a virtual table that is used as the input to the following step. These virtual tables 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 a query, the corresponding step is simply skipped. Following is a brief description of the different logical steps applied in both SQL Server 2000 and SQL Server 2005.

Brief Description of Logical Query Processing Phases

 

1.	FROM: A Cartesian product (cross join) is performed  between the first two tables in the FROM clause, and as a result, virtual  table VT1 is generated.
2.	ON: The ON filter is applied to VT1. Only rows for which  the <join_condition> is TRUE are inserted to VT2.
3.	OUTER (join): If an OUTER JOIN is specified (as opposed to  a CROSS JOIN or an INNER JOIN), rows from the preserved table or tables for  which a match was not found are added to the rows from VT2 as outer rows,  generating VT3. If more than two tables appear in the FROM clause, steps 1  through 3 are applied repeatedly between the result of the last join and the  next table in the FROM clause until all tables are processed.
4.	WHERE: The WHERE filter is applied to VT3. Only rows for  which the <where_condition> is TRUE are inserted to VT4.
5.	GROUP BY: The rows from VT4 are arranged in groups based  on the column list specified in the GROUP BY clause. VT5 is generated.
6.	CUBE | ROLLUP: Super groups (groups of groups) are added to  the rows from VT5, generating VT6.
7.	HAVING: The HAVING filter is applied to VT6. Only groups  for which the <having_condition> is TRUE are inserted to VT7.
8.	SELECT: The SELECT list is processed, generating VT8.
9.	DISTINCT: Duplicate rows are removed from VT8. VT9 is  generated.
10.	ORDER BY: The rows from VT9 are sorted according to the  column list specified in the ORDER BY clause. A cursor is generated (VC10).
11.	TOP: The specified number or  percentage of rows is selected from the beginning of VC10. Table VT11 is generated  and returned to the caller.

 Over!