------ SQL query procedure ------

MSSQL logical query steps from: Microsoft SQL Server 2005 Technical Insider: T-SQL Query

Various Stages of logical Query Processing

This section describes the phases involved in logical query processing. I will briefly describe each stage, and then introduce them in more detail in the following sections, and apply them to a sample query. When recalling the meaning and sequence of each stage, you can use this section as a quick reference.

Code List 1-1 lists the general query forms, and the sequence of each sub-statement being processed by logic is attached with the step number.

Code List 1-1 sequence number of logical Query Processing

 

(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 (10) order by <order_by_list>

 

Unlike other programming languages, SQL is most characteristic of code processing. In most programming languages, the code is processed in the encoding order, but in SQL, the first clause to be processed is the from clause. Although the first select statement appears, but it is almost always processed at the end.

Each step generates a virtual table, which is used as the input for the next step. These virtual tables are unavailable to callers (client applications or external queries. Only the table generated in the last step will be returned to the caller. If a clause is not specified in the query, the corresponding steps are skipped. Below is a brief description of each logical step used for SQL Server 2000 and SQL Server 2005. Later in this chapter, I will separately discuss the new steps in SQL Server 2005.

Introduction to the logical Query Processing Stage

If you think that the description of these steps is not significant, don't worry too much. They are just an overview. These steps are described in detail in the following section.

1. From: Perform the Cartesian Product (Cartesian Product) on the first two tables in the from clause to generate the virtual table vt1.

2. On: Apply the on filter to vt1. VT2 is inserted only when the rows are true.

3. outer (join): If outer join is specified (compared with cross join or inner join), the rows that do not match in the preserved table are retained as external rows and added to VT2 to generate T3. If the from clause contains more than two tables, perform steps 1 to 3 on the result table generated by the previous join and the next table until all tables are processed.

4. Where: Apply the where filter to vt3. Vt4 is inserted only when the row is set to true.

5. Group by: group the rows in vt4 by the column list in the group by clause to generate vt5.

6. cube | rollup: inserts supergroups into vt5 to generate vt6.

7. Having: Apply the having filter to vt6. Only groups that are set to true are inserted with vt7.

8. Select: process the select list and generate vt8.

9. distinct: Remove duplicate rows from vt8 to generate vt9.

10. Order by: sorts the rows in vt9 by column list in the order by clause to generate a cursor (VC10 ).

11. Top: select a specified number or proportion of rows from the beginning of VC10, generate table vt11, and return it to the caller.