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.