SQL Server Concatenation Operator – Part1

Hi Geeks,

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.

USE [AdventureWorks2012]

SELECT * FROM [Person].[Person] A
INNER JOIN [Person].[Person] B
ON A.BusinessEntityID = B.BusinessEntityID

ConcatMergeQueryPlan

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.

ConcatMergeQueryTextPlan

In tomorrow’s post we are going to see how do we derive to concatenation operator from here.

Happy learning!

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *