Various Stages of SQL Server Query Processing

Various Stages of SQL Server Query Processing

SQL is different from otherProgramming LanguageProcessing is the most obvious featureCode. In programming languages with large numbers, 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.

Each step generates a virtual table, which is used as the input for the next step. These virtual tables apply to callers (Client ApplicationsProgramOr external query) is not available. 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. The following is a brief description of each logical step for SQL Server 2000 and SQL Server 2005.

 

Introduction to the logical Query Processing Stage

1. from: RUN Cartesian Product (cross join) on the first two tables in the from clause to generate a virtual table vt1.
2. On: apply the on filter to vt1. VT2 is inserted only when is true.
3. outer (join): If outer join (compared to cross join or (inner join) is specified, the table (preserved table: the left external join marks the left table as a reserved table, the right external join marks the right table as a reserved table, and the full external join marks both tables as reserved tables) no matching rows are found to be added to VT2 as external rows. vt3. if the from clause contains more than two tables, repeat steps 1 to 3 for the result table generated by the previous join and the next table until all the tables are processed.
4. where: apply the where filter to vt3. Vt4. is inserted only when is true.
5. group by: group rows in vt4 by column list in the group by clause to generate vt5.
6. cube | rollup: inserts suppergroups into vt5 to generate vt6.
7. having: apply having filter to vt6. Vt7.
is inserted only when is set to true.
8. select: process the select list and generate vt8.
9. distinct: duplicate rows are removed from vt8 to generate vt9.
10. order by: sorts the rows in vt9 by the column list in the order by clause and generates a cursor (VC10).
11. top: select a specified number or proportion of rows from the beginning of VC10, generate table vt11, and return to the caller.

Note: Step 10: sort the rows returned in the previous step by column list in the order by clause, and return the cursor vc10. this step is the first and only step to use the column alias in the select list. Unlike other steps, this step returns a cursor instead of a valid table. SQL is based on the set theory. A set does not sort its rows in advance. It is only a logical set of members, and the order of members is irrelevant. You can return an object for sorting a table, including rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a correct understanding of SQL.

Because this step does not return the table (but returns the cursor), queries using the order by clause cannot be used as table expressions. Table expressions include views, inline Table value functions, subqueries, derived tables, and shared expressions. The result must be returned to the client application that expects a physical record. For example, the following derived Table query is invalid and an error is generated:

Select*From(SelectOrderid,CustomeridFromOrdersOrderOrderid)AsD
 

The following view also produces errors.

 
Create ViewMy_viewAsselect*FromOrdersOrderOrderid
 

In SQL, table expressions do not allow queries with an order by clause, but there is an exception in the T-SQL (applying the top option ).

Therefore, remember not to assume any specific sequence for the rows in the table. In other words, do not specify the order by Clause unless you are sure to order the rows. Sorting requires cost. SQL server needs to perform an ordered index scan or use a sort operator.
A piece of SQL code is recommended: transpose rows and columns

Code
/**/ /*Question: Suppose there is a student orders table (TB) as follows:
Name course score
Zhang San Language 74
James math 83
Zhang San physical 93
Li Si language 74
Li Si mathematics 84
Li Si physical 94

(The following result is displayed ):
Name, Chinese, Mathematics, Physics
----------------
Li Si 74 84 94
Zhang San 74 83 93
-------------------
*/  

Create   Table TB (name Varchar ( 10 ), Course Varchar ( 10 ), Score Int )
Insert   Into TB Values ( ' Zhang San ' , ' Chinese ' , 74 )
Insert   Into TB Values ( ' Zhang San ' , ' Mathematics ' , 83 )
Insert   Into TB Values ( ' Zhang San ' , ' Physical ' , 93 )
Insert   Into TB Values ( ' Li Si ' , ' Chinese ' , 74 )
Insert   Into TB Values ( ' Li Si ' , ' Mathematics ' , 84 )
Insert   Into TB Values ( ' Li Si ' , ' Physical ' , 94 )
Go  

-- SQL Server 2000 static SQL indicates that the course only includes three courses: Chinese, mathematics, and physics. (Same as below)
Select Name As Name,
Max ( Case Course When   ' Chinese '   Then Score Else   0   End ) Language,
Max ( Case Course When   ' Mathematics '   Then Score Else   0   End ) Mathematics,
Max ( Case Course When   ' Physical '   Then Score Else   0   End ) Physical
From TB
Group   By Name