Sql execution process description

The most obvious feature that distinguishes SQL from other programming languages is the order in which code is processed. In most programming languages, codes are processed in coding order, but in SQL language, the first clause to be processed is the FROM clause, and although the SELECT statement appears first, it is almost always processed last.

Each step generates a virtual table, which is used as input to the next step. These virtual tables are not available 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 step is skipped. What follows is a brief description of the various logical steps that apply to SQL Server 2000 and SQL Server 2005. Code highlighting produced by Actipro CodeHighlighter (freeware)

-->(8)SELECT (9)DISTINCT (11)

( 1) FROM [left_table]

(3) JOIN

(2) ON

(4) WHERE

(5) GROUP BY

(6) WITH

(7) HAVING

(10)ORDER BY

Introduction to the logical query processing stage (Execution order)

FROM: For the first two in the FROM clause The table performs Cartesian product (cross join) to generate virtual table VT1 ON: Apply ON filter to VT1. Only those rows for which is true are inserted into VT2. OUTER(JOIN): If OUTER JOIN (relative to CROSS JOIN or (INNER JOIN)) is specified, the reserved table (preserved table: left outer join marks the left table as a reserved table, and right outer join marks the right table as reserved table, a full outer join marks both tables as reserved (full outer join marks both tables as reserved). Rows that do not find a match will be added to VT2 as outer rows, generating VT3. If the FROM clause contains more than two tables, the results generated by the previous join will be Repeat steps 1 to 3 for the table and the next table until all tables are processed WHERE: Only rows with set to true are inserted into VT4.##. # GROUP BY: Group the rows in VT4 according to the column list in the GROUP BY clause to generate VT5. CUBE|ROLLUP: Insert the supergroup (Suppergroups) into VT5 to generate VT6. HAVING: Apply to VT6 HAVING filter. Only groups with set to true will be inserted into VT7. SELECT: Process the SELECT list, producing VT8. DISTINCT: Remove duplicate rows from VT8, producing VT9. ORDER BY: Sort the rows in VT9 by the column list in the ORDER BY clause to generate a cursor (VC10). TOP: Select a specified number or proportion of rows from the beginning of VC10 to generate table VT11, And return to the caller.

Note: Step 10, sort the rows returned in the previous step according to the column list in the ORDER BY clause, and return the cursor VC10. This step is the first and only step that can be used in the SELECT list. Column aliasing step. This step is different from the other steps in that it does not return a valid table, but returns a cursor. SQL is based on set theory. The set does not pre-order its rows, it is just the logic of the members. For collections, the order of the members does not matter. A query that sorts a table can return an object containing rows organized in a specific physical order. Understanding this step is fundamental to understanding SQL correctly. Because this step does not return a table (but a cursor), queries using the ORDER BY clause cannot be used as table expressions. Table expressions include: views, inline table-valued functions, subqueries, derived tables, and A common expression. Its result must be returned to the client application that expects a physical record. For example, the following derived table query is invalid and generates an error:

select *

from(select orderid,customerid from orders order by orderid)

as d

The following view will also generate errors

create view my_view

as

select *

from orders

order by orderid

In SQL, queries with ORDER BY clauses are not allowed in table expressions , but there is an exception in T-SQL (applying the TOP option).

So remember not to assume any specific order for the rows in the table. In other words, do not specify an ORDER BY clause unless you are sure you want the rows ordered. Sorting comes at a cost, and SQL Server needs to perform an ordered index scan or use a sort operator.