This article follows: SQL Server execution Plan operator detailed (1)-Assertion (Assert)
Objective:
According to the plan, this article began to tell the other operator concatenation (concatenation), the reader can be based on the word (both English and Chinese) first fantasy is what it is. In fact, it is very intuitive, is to connect things together, then let us look at the bottom of what even? How do you even? When do you even?
Brief introduction:
The concatenation operator is both a physical operator and a logical operator, called "concatenation" in the graphical execution plan of the Chinese version of SQL Server, and is called "concatenation" in other formats and English versions. Its icon is:, it scans multiple inputs and returns each scanned row. Typically used to implement union ALL in T-SQL. It can have multiple inputs, but only one output, as with multiple sets of union all, and eventually returns a result set, note that the "set/set" is used here and the relational database is based on set theory, so consider the problem with a set of thinking when using a relational database.Each operator in the execution plan implements three methods/functions: Init (), GetNext (), and Close (). As I said earlier, the concatenation operator is one of the operators that can accept multiple inputs, which are handled in the Init () method.In the Init () method, the concatenation is initialized and then the desired data structure is established. The GetNext () method is then run to read the first row and subsequent rows in the input set until all the data in the input collection is read.
Environment Construction:
The following creates a test table and loops through the 10000 rows of data.
Use Tempdbgoif object_id (' TEST ', ' U ') are not nulldrop table Testgocreate table TEST (ID INT Identity (1, 1) PRIMARY Key,nom e VARCHAR (+) DEFAULT NewID ()) Goset NOCOUNT Ongoinsert into Test defaultvalues GO 10000
Tandem Demo:
As mentioned earlier, concatenation is primarily used to implement the union ALL of T-SQL, so let's look at the union all case now:
Open the actual execution plan and run the following statement:
SELECT * from TestUnion allselect * from TestUnion allselect * from TestUnion allselect * from TEST
The implementation plan is as follows:
If you use SET SHOWPLAN_TEXT on to view it, you can see the following results:
The meaning of this figure is to plug the results of the 4 "Clustered Index Scan" into a result set, and then call the init () and GetNext () methods to traverse the data and output. It is also necessary to note that this operator is processed according to the order in which the result set appears in T-SQL, and in order to prove the idea, let's rewrite the statement:
SET showplan_text on Goselect * from Testwhere id<100union allselect * from Testwhere ID between 101 and 1000UNION Alls Elect * from Testwhere ID between 1001 and 5000UNION Allselect * from Testwhere ID >5001
Then look at the output:
The order of each clustered Index seek is consistent with the order in which the statements appear in comparison to the parameters. In addition, the reader may notice the last ordered FORWARD of each line, meaning that the order in which the indexes are scanned is in the order of the clustered index and scanned forward.
On this topic you can look at SQL Server Technology Insider main author Kalen Delaney's blog: Ordered seeks and Scans.
Summarize:This article mainly demonstrates the case of the concatenation operator and is mainly triggered by the union all in T-SQL. Since there is currently no data to show whether only union all will be used, so there is no absolute judgment, the reader only need to know the meaning of this operator, common scenarios can be. In addition, the reader can use the union to check the execution plan, in fact, the Union is not in series, because it essentially needs to go to the weight, so the use of different operators, such as the merge Join, followed by the introduction.The next article will cover:Compute scalar: Compute scalar
SQL Server Execution Plan operator detailed (2)--concatenation (concatenation)
