Thanks to Wayne for an awesome article! And thanks others for great discussion.
There's one point that no one picked up that I find inaccurate:
Note that the estimated execution plan for both table variables and temporary tables will show 1 row;
Agreed, the cardinality for the table variable is 1 row (without recompilation) but the temporary table??
select 1 id into #obs
union select 2
union select 3
union select 4
union select 5
and then examine the estimated execution plan for:
select * from #obs;
The full table scan of #obs has cardinality 5, not 1, even before the query is executed and the plan cached. Given that statistics are created for temporary tables this is exactly what I'd expect so am confused why you would suggest otherwise. What have I missed?