SQL Server Execution Plan operator detailed (2)--concatenation (concatenation)

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 tempdb

GO

IF object_id (' TEST ', ' U ') is not NULL

DROP TABLE TEST

GO

CREATE TABLE Test (

ID INT Identity (1, 1) PRIMARY KEY

, Nome VARCHAR (+) DEFAULT NewID ()

)

GO

SET NOCOUNT on

GO

INSERT into Test DEFAULT

VALUES

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 TEST

UNION All

SELECT * from TEST

UNION All

SELECT * from TEST

UNION All

SELECT * 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

GO

SELECT * from TEST

WHERE id<100

UNION All

SELECT * from TEST

WHERE ID between 101 and 1000

UNION All

SELECT * from TEST

WHERE ID between 1001 and 5000

UNION All

SELECT * from TEST

WHERE 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 UnionAll 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.

SQL Server Execution Plan operator detailed (2)--concatenation (concatenation)