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
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 FaceBook | Follow me on Twitter | Follow me on FaceBook
