CODEWITHSUNDEEP
sql-serverunion
Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

UNION and literal value, weird result

Today I encountered something weird (or I'm to tired to see something obvious)

Disclaimer: No need to continue reading... Yes, I was to tired and this is nonsense...

SQL-Server: 11.0.6251.0

I answered this question and saw, that all rows got an "E" in the first place.

This is the same in short

DECLARE @tblA TABLE(Id INT,SomeValue VARCHAR(100));
INSERT INTO @tblA VALUES (1,'blah');

DECLARE @tblB TABLE(Id INT,SomeValue VARCHAR(100));
INSERT INTO @tblA VALUES (2,'blub');

SELECT 'A' AS SourceFlag, Id, SomeValue
FROM @tblA
UNION ALL
SELECT 'B', Id, SomeValue
FROM @tblB

This I get back

SourceFlag  Id  SomeValue
A           1   blah
A           2   blub

Why didn't the second row get the "B"?

The execution plan:

enter image description here

One Compute Scalar shows [Expr1004] = Skalaroperator('A') the other one shows [Expr1009] = Skalaroperator('B').

The Concatenation defines the result as Union1010; Union1011; Union1012, while Union1010 is defined in [Union1010] = (Expr1004; Expr1009); [Union1011] = (Id; Id); [Union1012] = (SomeValue; SomeValue).

Upvotes: 2

Views: 249

Answers (1)

SQLChao
SQLChao

Reputation: 7847

No data was ever inserted into @tblB. ID 2 was inserted into @tblA.

DECLARE @tblB TABLE(Id INT,SomeValue VARCHAR(100));
INSERT INTO @tblA VALUES (2,'blub');

Upvotes: 4

Related Questions