Execution/Query Plan Operator – The Concatenation

Today let’s talk about Concatenation query plan operator. In execution plan we may have n number of operators that were used by SQL Server to satisfy a query. These operators are called physical operators. Now there are some operators which are logical as well as physical. Concatenation is one of those operators. It is logical as well as physical.

So what Concatenation operator do, Concatenation operator receives one or more input tables(streams) and returns all the rows from each input. So you must be getting what I am talking about. Yes you are right it is used for UNION ALL.

Lets jump on the demo and check out the execution plan-

--

--Create a simple table
CREATE TABLE Book
(
	 BookID INT
	,BookName VARCHAR(50)
	,Edition VARCHAR(10)
)
GO

--Insert some values
INSERT INTO Book(BookID,BookName,Edition)
VALUES
(100012,'SQL Server MVP Deep Dives','1st'),
(167012,'SQL Server MVP Deep Dives','Second'),
(132012,'SQL 2016','Third'),
(100222,'SQL SERVER 2014 - Hekaton','1st')
GO

--Create a clustered index
CREATE UNIQUE CLUSTERED INDEX Ix_BookId ON Book(BookId)
GO

--Lets execute the below query and check out the execution plan
SELECT BookID,BookName,Edition
FROM Book
UNION ALL
SELECT BookID,BookName,Edition
FROM Book

--

Textual Actual Execution Plan using SET Statistics PROFILE ON

Graphical Actual Execution Plan

The concatenation operator here receives the result of all clustered index scan and combines them together to make a single Data Set. Here SQL Server executes the plan in the order that appears in the execution plan.( In this case Top to Bottom )

I hope you have enjoyed the article. Thanks for reading !

-Pawan Khowal

MSBISkills.com