I have a table with a few dozens of rows. Simplified setup is following
CREATE TABLE #data ([Id] int, [Status] int);
INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);
And I have a query that joins this table to a set of table value constructed rows (made of variables and constants), like
DECLARE @id1 int = 101, @id2 int = 105;
SELECT
COALESCE(p.[Code], 'X') AS [Code],
COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
(@id1, 'A'),
(@id2, 'B')
) p([Id], [Code])
FULL JOIN #data d ON d.[Id] = p.[Id];
Query execution plan is showing that optimizer's decision is to use FULL LOOP JOIN
strategy, which seems appropriate, since both inputs have very few rows. One thing I noticed (and cannot agree), though, is that TVC rows are being spooled (see area of the execution plan in the red box).
Why optimizer introduces spool here, what is the reason to do it? There is nothing complex beyond the spool. Looks like it is not necessary. How to get rid of it in this case, what are the possible ways?
The above plan was obtained on
Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)