Concatenation operator in SQL Server execution plan

Name itself concatenation means adding the two or more result set in to a single result set. The Concatenation Operator in SQL server execution plan is used to combine the result from queries where multiple queries or sets of data are combined.

It generate on below scenario’s 

Ø  Union all

Ø  Union

Read: Difference between UNIONand UNION ALL

https://bageshkumarbagi-msbi.blogspot.com/2016/05/difference-between-union-and-union-all.html

Icon of Concatenation Operator

Let’s see the example

Union

Run the below query

select * from Sales.SalesOrderHeader where ShipDate<'2013-07-17' union select * from Sales.SalesOrderHeader where ShipDate>'2013-07-17'

See the execution plan

See one simpler example.

select 'Bagesh' as name union select 'Kumar'

 See the execution plan

See the example of union all

Run the below query

select * from Sales.SalesOrderHeader where ShipDate<'2013-07-17' union all select * from Sales.SalesOrderHeader where ShipDate>'2013-07-17'

See the execution plan