T-SQL logical query Processing

Introduction

This article is a reading note for the first chapter of the Microsoft SQL SERVER 2008 Technical Insider T-SQL query, which focuses on analyzing the order in which clauses in a SQL query are executed. If you have a clear picture of this, you can skip the article, and if you have more precious time, you can take a look at the flowchart; If you want to know more about it, you can read the "T-SQL query" book.

I wrote a few test questions below, if you do not know the reason for these problems, then I suggest you look at this book, and then should look at this article. (The more I want to go far, the more we should pay attention to the basis)

 Test Questions

1,   for full join, Cross joins (SELECT * from orders, Customers ) Do you have a clear understanding of this?
SELECT * from Orders cross JOIN Customers on Orders.customerid=customers.customerid; (wrong)

2,   when you create a view, if you do not include top, Why can't I use ORDER by?
CREATE VIEW V as SELECT * from   orders ORDER by orderid ; (wrong)
CREATE VIEW V as  Select TOP Ten * from Orders ORDER by ORDERID  ; (pair)

3,   Why can't aliases in the select list be used in the WHERE clause?
select customerid as Id From Orders WHERE id>5; (wrong)

4. In a query statement that uses the GROUP BY clause, why is the column in the having or SELECT clause only a column in the GROUP BY clause or wrapped in an aggregate function?
SELECT orderId,customerId From orders GROUP by orderId Having customerId = 15; (wrong)

5, when the multi-table joins the query, under what condition the filter condition adds the same function on the Where or on?

Logical Query Processing

Step Number:

Flow chart:

Introduction to the processing phase:

(1 the From processing table operator generates the virtual table VT1. In the Join operator example, join has three sub-stages

(1-j1 Cartesian product Two tables perform a Cartesian product (cross join), generating a virtual table vt1-j1.

(1-j2 ) on Sieve The vt1-j1 is filtered by the criteria in the ON clause, and only the rows with the condition true can be inserted into the vt1-j2.

(1-j3 Add an outer row if you specify a outer join, the table is reserved (for example, when you use a LEFT JOIN, the left table is a reserved table), and the row is added to Vt1-j2 as an outer row to generate VT1-J3.

(2 where the rows in VT1 are filtered based on the conditions in the WHERE clause, only the rows with the condition true are inserted into VT2.

(3 group by groups the rows in the VT2, generating VT3. Finally, there is only one result row for each grouping.

(4 having to filter groups in VT3 based on the conditions of the HAVING clause, only groups with the condition true are inserted into VT4.

(5 The Select handles the elements in the SELECT clause to produce VT5.

(5-1 evaluates the expression that evaluates the column in the Select, generating vt5-1.

(5-2 DISTINCT Deletes duplicate rows from the vt5-1 and generates VT5-2.

(5-3 TOP Selects the first specified number or percentage of rows from vt5-2 to generate vt5-3, based on the logical ordering defined by the ORDER BY clause.

(6 order by sorts the columns in vt5-3 based on the columns in the ORDER BY clause, generating a cursor VC6.

Introduction to Logical query processing of other table operators

APPLY

Apply involves one or two of the following two steps (depending on the Apply type):

1. A1: Use each row in the left table with the expression on the right and the last combination to generate the virtual table A1.
2. A2: Add an external row. (for outer apply)

PIVOT

Example: SELECT * from Orders PIVOT (SUM (val) for OrderYear in ([2006],[2007])) as P  

pivot involves the following three logical phases:

1. P1: grouping. the rows in orders are implicitly grouped by the grouping of all columns that are not entered as pivot, and the virtual table P1 is generated in groups. (You can see from the sample query statement that the pivot operator refers to the two columns of orders as input parameters (Val,orderyear)).
2. P2: extension. extend the values in the in list to their corresponding target columns. Logically, a case expression is used for each target column specified in the IN clause: Case when orderyear= 2006 then Val END
3. P3: Aggregation. applies the specified aggregate function to each case expression to produce a result column. SUM (case if orderyear= 2006 then Val END) as [2006].

UNPIVOT

Example: SELECT * from Data source table UNPIVOT (Val for OrderYear in ([2006],[2007])) as U

Unpivot involves the following three logical phases:

1. U1: Generates a copy. The column names in the loop in, and each column generates a copy of the left table. The virtual table generated in this step is shown in table U1.
2. U2: Extracts the element. Extracts a value from the source column that corresponds to the transformation element represented by the current copy of the row. The virtual table generated in this step is shown in table U2.
3. U3: Delete the line with NULL. The virtual table generated in this step is shown in table U3.

T-SQL logical Query processing