Examples of various stages of SQL Server Query Processing (SQL Execution sequence)

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 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. The following is a brief description of each logical step for SQL server 2000 and SQL Server 2005.
Copy codeThe Code is as follows:
(8) SELECT (9) DISTINCT (11) <Top Num> <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>

Introduction to the logical Query Processing Stage
1. FROM: Perform 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 <join_condition> is true.
3. OUTER (JOIN): If outer join (compared with cross join or (inner join) is specified, preserved table: The left outer 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.) unmatched rows are not found and added to VT2 as external rows, generate 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 tables are processed.
4. WHERE: Apply the WHERE filter to VT3. Vt4is inserted only for rows with <where_condition> true.
5. group by: GROUP the 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 the HAVING filter to VT6. Vt7is inserted only when 8. SELECT: process the SELECT list, process various aggregate functions, 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 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:
Copy codeThe Code is as follows:
Select *
From (select orderid, customerid from orders order by orderid)
As d

The following view also produces errors.
Copy codeThe Code is as follows:
Create view my_view
As
Select *
From orders
Order by orderid

Error message:Msg 1033, Level 15, State 1, Procedure my_viewasselect, Line 2The order by clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or for xml is also specified. 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
Copy codeThe Code is as follows:
/* Problem: Assume that there is a student renewal 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', 'China', 74)
Insert into tb values ('zhang san', 'mat', 83)
Insert into tb values ('zhang san', 'Physical ', 93)
Insert into tb values ('Li si', 'China', 74)
Insert into tb values ('Li si', 'mat', 84)
Insert into tb values ('lily', '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 'China' then score else 0 end) language,
Max (case course when 'mate' then score else 0 end) math,
Max (case course when 'physical 'then score else 0 end) Physical
From tb
Group by name