I am trying to understand the Execution plan for the following query.
I am using the StackOverflow2010 database to create two tables with some date columns:
USE StackOverflow2010
CREATE TABLE TableA
(
EventDate DATETIME NULL
)
INSERT INTO TableA
SELECT TOP 54000
CreationDate
FROM Users
CREATE TABLE TableB
(
EffectiveStartDate DATETIME NULL,
EffectiveEndDate DATETIME NULL
)
INSERT INTO TableB
SELECT TOP 24000
CreationDate,
LastActivityDate
FROM dbo.Posts
Then an index to support our query
CREATE INDEX IX_CreationDate ON TableB
(
EffectiveEndDate
)
INCLUDE
(
EffectiveStartDate
)
The query is
SELECT a.EventDate,
b.EffectiveStartDate,
b.EffectiveEndDate
FROM TableA a
LEFT JOIN TableB b
ON a.EventDate <= b.EffectiveEndDate OR
b.EffectiveEndDate IS NULL
and the execution plan is here. I am trying to understand how SQL Server has implemented the ON predicate. It looks like the index seek is seeking between 2 scalar operators (Expr1013 and Expr1014)
and I am trying to work out what these two values are.
We can see table A is scanned and for each row out of that scan, the following process appears to be followed
Constant Scan - Creates a "blank Row"
Compute Scalar adds scalar values to the row
[Expr1008] = Scalar Operator([StackOverflow2010].[dbo].[TableA].[EventDate] as [a].[EventDate]), -- the value for this nested loop iteration
[Expr1009] = Scalar Operator(NULL),
[Expr1007] = Scalar Operator((22))
Constant Scan - Creates a "blank Row"
Compute Scalar adds scalar values to the row
[Expr1011] = Scalar Operator(NULL),
[Expr1012] = Scalar Operator(NULL),
[Expr1010] = Scalar Operator((60))
Concatenation unions to the two rows into a single result set
[Expr1013] = (Expr1008, Expr1011),
[Expr1014] = (Expr1009, Expr1012),
[Expr1015] = (Expr1007, Expr1010)
I believe this means that after the concatenation operator, we have a table like structure that looks like this (I have assumed a value of 01-01-1900T00:00:00 has been returned from the EventDate column in this iteration)
+---------------------+----------+----------+
| Expr1013 | Expr1014 | Expr1015 |
+---------------------+----------+----------+
| 01-01-1900T00:00:00 | NULL | 22 |
| NULL | NULL | 60 |
+---------------------+----------+----------+
I am confused about what is happening with the Compute Scalar that follows the concatenation operator
[Expr1016] = Scalar Operator(((4)&[Expr1015]) = (4) AND NULL = [Expr1013]),
[Expr1017] = Scalar Operator((4)&[Expr1015]),
[Expr1018] = Scalar Operator((16)&[Expr1015])
I assume this will again return a two row table with three "columns" - Expr1016, Expr1017, Expr1018 and two rows? What will the value of the rows be? Is the & a bitwise and operator?
I am also confused as to what purpose the scalar values returned fron the initial compute scalars (22 and 60) serve.
Can anyone clarify if my understanding of the plan upto the concatenation operator is correct and how from there SQL Server calculates the two predicates used in the index seek?
