SQL Server concatenation operator receives one or more inputs and returns all the rows from each input stream. In general we can see this in action while using UNION ALL. We can say, concatenation can receive more than one input and is both logical and physical operator.
In today’s blog post we will first explore some basic but important points on execution plan operators then tomorrow go into detail on the subject.
All the operators used in execution plans implements three methods, to name them Init(), GetNext() and Close(). If any operator receives more than one inputs, Init() will process them and concatenation is one of such operators.
Concatenation operator will be initialized by calling Init() method. This will also set up data structures. GetNext() method is called next then to read subsequent rows of the input data and continues until it has read all rows from the input data.
Let’s take the following query statement as the sample for today before jumping on to the actual operator.
SELECT * FROM [Person].[Person] A
INNER JOIN [Person].[Person] B
ON A.BusinessEntityID = B.BusinessEntityID
As can be seen, query statement is using Merge operator (we are going to cover in future) to join the tables and is receiving two inputs i.e. Person.Person table twice and same can be observed in text plan as well.
In tomorrow’s post we are going to see how do we derive to concatenation operator from here.