{"id":864,"date":"2010-04-22T00:00:00","date_gmt":"2010-04-22T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/showplan-operator-of-the-week-concatenation\/"},"modified":"2021-08-16T15:02:17","modified_gmt":"2021-08-16T15:02:17","slug":"showplan-operator-of-the-week-concatenation","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/showplan-operator-of-the-week-concatenation\/","title":{"rendered":"Showplan Operator of the Week – Concatenation"},"content":{"rendered":"
\n

\"1010-Contatenation001.gif\" Concatenation<\/p>\n

Showplan Operators are used by SQL Server’s Query Optimizer (QO)  to perform a particular operation within a query plan. A query plan will usually contain several of these physical operators. Each physical operation is represented in the Query Plan by an operator, and each operator is shown in the graphical execution plan by an icon. This week we’ll be featuring the concatenation showplan operator. Its behavior is quite simple; it receives one or more input streams and returns all the rows from each input stream in turn. We can see its effect whenever we use the Transact-SQL UNION ALL<\/b> command.<\/p>\n

Concatenation is a classic operator that can receive more than one input. It is both a logical and a physical operator.<\/p>\n

Before we start to talk about concatenation, we need to understand some important points about showplan operators and execution plans.<\/p>\n

All operators used in execution plans, implement three methods called Init(),<\/b> GetNext()<\/b> and Close().<\/b> Some operators can receive more than one input, so, these inputs will be processed at the Init()<\/b> method. The concatenation is one example of these operators.<\/p>\n

At the Init()<\/b> method, the concatenation will initialize itself and set up any required data structures. After that, it will run the GetNext() method to read the first or the subsequent row of the input data, it runs this method until it has read all rows from the input data.<\/p>\n

Let’s take the following query as a sample:<\/p>\n

The following script will create a table TabTeste and populate with some garbage data.<\/p>\n

USE tempdb \nGO \nCREATE TABLE TABTeste(ID   Int Identity(1,1) PRIMARY KEY, \n                      Nome VarChar(250)      DEFAULT NewID()) \nGO \nSET NOCOUNT ON \nGO \nINSERT INTO TABTeste DEFAULT VALUES \nGO 10000 \n<\/pre>\n
The script above will populate 10000 rows at the TabTeste<\/b> table. Now let’s run one query sample to look at the execution plan.<\/p>\n
SELECT * \n  FROM TABTeste a \n INNER JOIN TABTeste b \n    ON a.ID = b.ID \n<\/pre>\n
Graphical execution plan:<\/p>\n
\"1010-Contatenation002.jpg\"<\/p>\n
\n
Text execution plan:<\/p>\n
SELECT * FROM TABTeste a INNER JOIN TABTeste b ON a.ID = b.ID \n<\/pre>\n<\/p>\n
\n
 |--Merge Join(Inner Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([TABTeste].[ID] as [b].[ID]=[TABTeste].[ID] as [a].[ID]))       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_] AS [b]), ORDERED FORWARD)        |--Clustered Index Scan(OBJECT:([TABTeste].[PK_] AS [a]), ORDERED FORWARD)\n\n<\/pre>\n<\/div>\n
As we can see, this query is using one operator called Merge<\/b> to join the tables, in the plan, the Merge<\/b> operator is receiving two inputs (the table TabTeste<\/b> twice).<\/p>\n
The operator of the week, Concatenation<\/b>, is a good example of an operator that receives more than one input. If, for example, we run the following query, we will see that it receives fours inputs.<\/p>\n
SELECT * FROM TABTeste \nUNION ALL \nSELECT * FROM TABTeste \nUNION ALL \nSELECT * FROM TABTeste \nUNION ALL \nSELECT * FROM TABTeste \n<\/pre>\n
Graphical execution plan:<\/p>\n
\"1010-Contatenation004.jpg\"<\/p>\n<\/p>\n
Text execution plan:<\/p>\n
  |--Concatenation \n       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_])) \n       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_])) \n       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_])) \n       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_]))\n<\/pre>\n
The  concatenation operator receives the result of all “clustered index scan<\/b>“  and copies all the rows to one output calling the methods Init()<\/b> and GetNext().<\/b> These methods are called to each Input.<\/p>\n
The Query Processor will execute this plan in the order that the operators appear in the plan, the first is the top one and the last is  the end one.<\/p>\n
That’s all folks, I see you next week with more “Showplan Operators”.<\/p>\n
If you missed last week’s thrilling Showplan Operator, The Assert, you can see it here<\/a> <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Fabiano continues in his mission to describe, one week at a time, all the major Showplan Operators used by SQL Server’s Query Optimiser to build the Query Plan. This week he gets the Concatenation operator .…<\/p>\n","protected":false},"author":65554,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4178,4149,5084,5162,4150,4151],"coauthors":[],"class_list":["post-864","post","type-post","status-publish","format-standard","hentry","category-learn","tag-bi","tag-learn-sql-server","tag-optimiser","tag-showplan","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/864","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/65554"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=864"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/864\/revisions"}],"predecessor-version":[{"id":92147,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/864\/revisions\/92147"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=864"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=864"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=864"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=864"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}