SQL Server Concatenation Operator – Part3

Hi Friends,

This is the third part on SQL Server Concatenation operator and if you missed my earlier posts, you can read Part1 and Part2 to understand the background of this blog post.

We saw that, concatenation operator reads each of its input in sequence then produce its output. We can say it is an n-ary physical operator i.e. it can have 2..n inputs. Today, let us slightly modify our query from yesterday then understand the internal workings of this operator.

USE [AdventureWorks2012]

SELECT * FROM Person.Person WHERE Person.BusinessEntityID = 20
UNION ALL
SELECT * FROM Person.Person WHERE Person.BusinessEntityID = 19
UNION ALL
SELECT * FROM Person.Person WHERE Person.BusinessEntityID = 21
UNION ALL
SELECT * FROM Person.Person WHERE Person.BusinessEntityID = 22

ConcatISKGraphicalPlan

 

ConcatISKTextPlan

Both graphical and text plan above produced a separate index seek on BusinessEntityID in the same order as specified in the query reading top down.  The topmost index seek is for the BusinessEntityID 20, then 19, 21 and 22.

As mentioned earlier, concatenation operator reads its inputs in sequence then produce the output, so we can say there is a good chance that the result set will show rows for 20, then 19, 21 and 22 though we cannot guarantee but fair to say this is how this operator works.

I’ll be back tomorrow with a new operator tomorrow, watch out this space for more.

Happy Learning!

Regards,

Kanchan

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

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 *