T-SQL statement Query Execution order

SQL execution Order

Reprinted from: http://www.cnblogs.com/knowledgesea/p/4177830.html

Objective

Database query execution, is undoubtedly one of the programmer's necessary skills, but the process of database query execution is gorgeous and colorful, but it is very rarely understood, today brother to take you to take you fly, deep into the context of this SQL query, for the query performance optimization processing to play a foundation, perhaps interview you will encounter, Prevention is not kneeling or look at it.

This blog, abandon query optimization performance, as its basis, only for the query process to explain the analysis.

This blog explains the process of

1, the previous identified SQL statement, showing the process of query execution

2. Previous flowchart

3, to do an example of step-in-depth analysis to help understand

4, do a load of the summary of forced

SQL query statement processing steps, code listing

--Query combination field (5) Select (5-2) distinct (5-3) Top (<top_specification>) (5-1) <select_list>--table (1) from (1-J) < Left_table><join_type> join <right_table> on <on_predicate>        (1-a) <left_table>< Apply_type> apply <right_table_expression> as <alias>        (1-p) <left_table> pivot (<pivot_ specification>) as <alias>        (1-u) <left_table> Unpivot (<unpivot_specification>) as <alias >--query Conditions (2) where <where_pridicate>--Group (3) group by <group_by_specification>--Grouping conditions (4) having
Description
1, order for there are 1-6, 6 large steps, then subdivide, 5-1,5-2,5-3, by small variable large order, 1-j,1-a,1-p,1-u, for parallel order. If it's not clear enough, then I'm going to take a look at the flowchart.
2, the implementation process will also generate a number of virtual tables (mentioned below) to match the final correct query.
SQL query statement processing steps, flowchart
instance preparation, creating a table, inserting data, writing an instance query statement to parse
1. Create 2 Tables First
2, create two tables, and insert table data, the script is as follows
View Code
3. Write the query statement we want to parse, that is, the instance statement to be queried in this article.
Select Top (4)  
Where M.id>0group by status have Status>=0order by Maxmemberid ASC
Step-by-steps analysis of instance statements
The first step, starting from the from.
1.1. Loading the left table
Query Result: Member all data in the table
1.2, this should be right outer join, but here is defined in SQL is broken down into 2 steps, that is, join, right outer join. The expression keyword is left-to-right and executed sequentially.
Query Result: A Cartesian collection of two tables, stored in virtual table VT1. Here you may not understand what is called Descartes set, I make an analogy to say, also hope not to abandon, is the child handshake problem, Class A has 3 students (as a table of three data), Class B has 2 students (as another table of 2 data). Class B children with a class of children to the party, first of all to ensure that with another class of students I hand, then the intersection of the set is (2*3=6) has 6 different tracks. The collection of this trajectory is the Cartesian set. If you don't understand, I'll say it again, the first data in the M (5 data) table is held with all the data in the O (7) table, there are 7, and then there are 35 different data. The null value here is also to be added in.
1.3. On filter
Query results: From the previous step of the Cartesian collection of 35 data deleted out of the unmatched row, you get 5 data, stored in virtual table Vt2
1.4. Add an external row (outer row)
The result of the query is: the right table (order) is the reserved table, and the remaining data is added back to the virtual table in the previous step VT2, generating the virtual table VT3.
Part two, into the where phase
where m.id>0
Query results: stored in the virtual table VT4, to filter the result set of true, add a memory point here, that is, where the filter is deleted as permanent, while the filter on the delete is temporary, because after filtering, it is possible to go through outer add external rows, re-load the data back, And where is not.
Part III, GROUP by group
Group BY Status
Query results: Deposit VT5, starting with the value of the status column, that is, the status column, the value is divided into a group, where two null in the three-value logic is considered true. Three-valued logic: True,false,null. This three-value, NULL is unknown, is the logical characteristic of the data, where two null equals ture, and in some places false. This you Baidu under to see a lot of explanation.
Fourth step, having a filter
Having status>=0
Query Result: Filter the group data of good group, remove the missing condition
Fifth step, select query cherry-Picking computed columns
5.1. Calculation expressions
Select Status, Max (M.id)
Query Result: Calculates the maximum number of m.id in each group from the data in the split group, listing the columns to filter for display.
5.2, DISTINCT filter repeat
5.3, top combined order by filter how many rows, but the data here is not sorted just how many rows of data listed.
Sixth, order by sort display.
The summary of the egg pain, the loading force is a basis
This blog reference: Microsoft SQL Server 2008 Technology Insider: T-SQL query, thank you for reading, (C #). NET technology share QQ Group: 232458226, Welcome to join.
T-SQL statement Query Execution order