Logical query processing steps for SQL Server 2008 _mssql

Recently, I have just received a copy of Microsoft SQL Server 2008 English version of the book from the most valuable expert project group-"Inside Microsoft 2008:t-sql Querying" (http:// www.microsoft.com/learning/en/us/book.aspx?ID=12804&locale=en-us), roughly turned over and found a lot of changes in content, both new chapters, but also based on SQL Server 2005 or 2008 new technology to rewrite the SQL query example.

I first on the logical query processing steps to introduce you to the difference between the two books, because I have just begun to read, and gradually introduced to you.

We know that the logical processing of queries is done in phases, and each phase produces a virtual table that acts as input for the next stage. However, the virtual tables generated during the intermediate stages of these procedures are not available for querying users, and only the virtual tables (that is, the query results) generated by the last stage are returned to the query user.

In the SQL Server 2005 version of the book, the logical handling of a join query is divided into 10 steps:

(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

(a) Order by <order_by_list>

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 steps include the child steps.

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

(1) From (1-j) <left_table> <join_type> joins <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

(6) ORDER BY <order_by_list>

The following figure describes the process of each processing 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, which have been replaced by the grouping SETS, rollup, and cube operators of the GROUP BY clause in SQL Server 2008, are no longer recommended for use with the ISO standard ROLLUP, with Cube, and all syntax. However, this does not affect the order of logical processing.

Following is a description of each step in the logical process, note the build steps for the virtual table (VTN):

· Step 1 (from): This step is used to validate the source table for the query and to work with table operators. Each table operator is applied to a series of child steps. For example, the following steps are involved in the (1-j) step above for joins. When these steps are complete, the virtual table VT1 is generated.

Ø (1-J1): Performs the left_table and right_table two tables cross joins (the flute Descartes product), produces the virtual table vt1-j1;

Ø (1-J2): Apply on filter to the Flute Descartes product, generate virtual table vt1-j2;

Ø (1-J3): If an outer join, the outer row filtered by on is added to the vt1-j2 in this step to generate the vt1-j3. Otherwise, the step is skipped.

· Step 2 (where): Apply a where filter to VT1 and insert rows that match the filter criteria into VT2.

· Step 3 (Group BY): Groups the rows in the VT2 by the list of columns in the GROUP BY clause, generating VT3. If the statement contains a with cube or with ROLLUP, the grouped statistic results are added to the total and then inserted into the VT3 to generate the VT3-RC.

· Step 4 (Having): Apply having a filter to VT3 to insert rows that match the filter criteria into VT4.

· Step 5 (SELECT): Processes 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 the vt5-2, based on the collation specified in the ORDER BY clause.

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