NestedConcatenationCardinalityEstimates.sql

SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n
INTO #numbers
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
GO

SELECT COUNT(*)
FROM (
       SELECT *
       FROM (
              SELECT * FROM #numbers
              UNION ALL SELECT * FROM #numbers
       ) x1
       WHERE n BETWEEN 1 AND 10000 -- Note that commenting out this line allows the nested Concatenation operator to be eliminated
       UNION ALL
       SELECT * FROM (
              SELECT * FROM #numbers
              UNION ALL SELECT * FROM #numbers
       ) x2
       WHERE n BETWEEN 1 AND 10000 -- Note that commenting out this line allows the nested Concatenation operator to be eliminated
) x
JOIN #numbers r
       ON r.n = x.n
OPTION (QUERYTRACEON 9481) -- Optionally, use SQL 2012 CE
-- Row estimates on the Hash Match output
-- 2012, nested concat: 1 estimated row, 40000 actual rows
-- 2012, single concat: 16007.2 estimated rows, 40000 actual rows
-- 2014, nested concat: 40000 estimated rows, 40000 actual rows
-- 2014, single concat: 40000 estimated rows, 40000 actual rows