SQL Server Run plan operator specific explanation (2)-concatenation (concatenation)

This article follows: SQL Server run plan operator specific explanation (1)-Assertion (Assert)

Objective:
According to the plan. This article starts with another operator concatenation (concatenation). Readers are able to imagine what they are doing, based on the word (both in English and Chinese). In fact, it is very intuitive, is to connect things together. So let's take a look at what the hell is going on? How do you even? When do you even?
Brief introduction:
The concatenation operator is both a physical operator and a logical operator. In the Chinese version of SQL Server, the graphical run plan is called "concatenation" and is called "concatenation" in other formats and English version numbers.

Its icon is:. It scans multiple inputs and returns each scanned row.

It is often used to implement union ALL in T-SQL. It can have multiple inputs, but there is only one output, just like multiple sets of union all, and finally returns a result set, note that the "set/set" is used here, and the relational database is based on set theory. So when using a relational database, consider the problem with a set of thinking. each operator in the run plan. To implement 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 first and possibly rows in the input set are then read in the Run GetNext () method. Until all the data in the input collection is read.

Environment Construction:
The following creates a measured frequently 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 now look at the union ALL situation:
Open the actual run schedule and run the following statement:

SELECT * from TestUnion allselect * from TestUnion allselect * from TestUnion allselect * from TEST

The run schedule is as follows:


Suppose you see the following results for example using SET SHOWPLAN_TEXT on:


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 based on the order of occurrence of the result set in T-SQL, in order to prove the idea, let us 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:


Compare the parameters to each clustered the order of Index seek and the order in which the statements appear are consistent. 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 accordance with 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. There is no absolute inference that the reader simply needs to know the meaning of the operator and the common scenario, since there is no data to show whether the union all is used at the moment. In addition, the reader can use union to check the run schedule. In fact, union is not connected in series, because it essentially needs to be weighed. So the use of different operators to implement. The merge Join, for example, might be introduced again.The next article will cover:Compute scalar: Compute scalar

SQL Server Run plan operator specific explanation (2)-concatenation (concatenation)