T-SQL query reading notes Part 1. How much does the logical query process know?

First, about T-SQL

T-SQL is an MS-SQL extension of the ANSI and ISO SQL standard, formally known as Transact-SQL, but is called T-SQL by a general programmer.

Second, the logical query processing each stage 2.1 logical query Processing process overview

2.2 Logical Query processing phase interpretation

(1) From: Identifies the source table of the query and handles table operators. Each operator applies a series of sub-stages. eg. the stage involved in join operation is the Cartesian product, the on filter, and the addition of the outer row. The from phase generates a virtual table, tentatively VT1.

• • (1-J1) Cartesian product: Performs a Cartesian product (cross join) on the two tables involved, generating a virtual table vt1-j1.
  • (1-J2) on filter: The rows in the vt1-j1 are filtered according to the predicates that appear in the ON clause. Only rows that have the predicate value of true can be inserted into vt1-j2.
  • (1-J3) Add an outer row: If you specify a outer join (relative to a cross join or inner join), the matching row is not found in the reserved table (preserved table), and is added to Vt1-j2 as an outer row, resulting in vt1-j3.

(2) WHERE: The rows in VT1 are filtered based on the predicate that appears in the WHERE clause. Only rows that have the predicate evaluate to true will be inserted into the VT2.

(3) Group by: The rows in VT2 are grouped to generate VT3, according to the list of column names specified in the GROUP BY clause. Finally, there is only one result row for each grouping.

(4) Having: The grouping in VT3 is filtered according to the predicate appearing in the HAVING clause. The VT4 is inserted only if the predicate evaluates to a line that evaluates to True.

(5) Select: Processes the elements in the SELECT clause, producing VT5.

• • (5-1) Calculation expression: Computes an expression in the select list, generating a vt5-1.
  • (5-2) DISTINCT: Delete duplicate rows in vt5-1, generate vt5-2.
  • (5-3) TOP: Generates vt5-3 from the vt5-2 by selecting a line from the previous specified quantity or percentage, based on the logical ordering defined by the ORDER BY clause.

(6) Order by: The row in vt5-3 is sorted according to the list of column names specified in the ORDER BY clause, and the cursor VC6 is generated.

Iii. Query Example 3.1 example scenario

Suppose there are two tables: Customers and Orders, and the table structure and data are as follows:

　　

Here we look for customers from Madrid with fewer than 3 orders, and the query code and results are as follows:

　　

3.2 Interpretation of each stage

(1) from stage:

 from  as C    Left OUTER JOIN  as O      on = O.customerid

　　step 1-j1= Cartesian product

This first does not consider the left OUTER, which forms the virtual table vt1-j1 after the join cross join:

　　

　　Step 1-j2=>on Filter

The on filter works by filtering out all rows in the virtual table vt1-j1 that were generated in the previous step and outputting them to the new virtual table vt1-j2, only those rows that make C.customerid = O.customerid true.

　　

　　Step 1-j3= Add an external row

This step will only occur in an external link (OUTER JOIN). Here is: Customers as C left OUTER JOIN Orders as O, which is customer as the reserved table. The final virtual table vt1-j3 is as follows:

　　

　　*. the customer is here as a reserved table, so fissa does not satisfy the on filter, but it is also added to the virtual table.

(2) where phase:

WHERE = ' Madrid '

At this stage, the line that the customer is mrphs in VT1 is removed (because its cityid is not Madrid), the VT2 is generated as follows:

　　

the difference between on and where : where the deletion of a row is final, and the delete on row is not, so step 1-j3 add the outer row and add it back. In addition, this logical difference exists between on and where when an outer join is used.

(3) GROUP by stage:

GROUP  by C.customerid

This step reorganizes the data rows in the VT2 in groups to get VT3 as shown:

　　

(4) Having stage:

 having COUNT < 3

This step is filtered from VT3, and only the group that makes count (O.orderid) <3 A logical value of TRUE will enter VT4. Having a filter is the only filter that can be used to group data.

　　

The Count (*) is not used here because the count (*) in the outer join also counts the external rows, such as the number of orders for Fissa to 1, which is obviously wrong.

(5) Select stage:

　　step 5-1= = Calculate expression

SELECT COUNT  as Numorders

Get vt5-1

　　

　　Step 5-2= apply DISTINCT clause

This example wood has a distinct clause, so vt5-1 does not change.

　　Step 5-3= Apply Top options

The top option is a feature that is unique to T-SQL, allowing you to specify the number of rows or percentages to return. However, this example also does not specify top, which is estimated vt5=vt5-1.

(6) ORDER by stage:

ORDER  by Numorders

This step will sort the VT5 and return the cursor VC6. The ORDER by clause is also the only step that can reuse the column aliases created in the select list .

　　

Resources

　　

[US] Itzik Ben-gan, Chengpaodong, Microsoft SQL Server 2008 Insider: T-SQL query

Zhou Xurong

Source: http://edisonchou.cnblogs.com

The copyright of this article is owned by the author and the blog Park, welcome reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to give the original link.

T-SQL query reading notes Part 1. How much does the logical query process know?