Bits of .NET

🔧 The Table Variable the Optimizer Refused to Study

A table variable and a temp table can hold identical rows and support nearly identical syntax, but the query optimizer treats them very differently: a temp table gets real statistics, the same way a permanent table does, while a table variable gets none at all. Whatever the actual row count ends up being, any query joining against a table variable is planned around an assumption of roughly one row, which is a perfectly reasonable default for a small lookup list and a genuinely bad one the moment that table variable ends up holding tens of thousands of rows instead.

🔎 The Problem

DECLARE @OrderIds TABLE (Id INT PRIMARY KEY);
INSERT INTO @OrderIds SELECT Id FROM Orders WHERE Status = 'Pending';
-- @OrderIds might hold 50,000 rows after this - but SQL Server
-- maintains NO statistics on table variables, so the optimizer
-- always assumes roughly 1 row when building a plan that joins
-- against it, regardless of how many rows actually ended up inside.

SELECT o.*
FROM @OrderIds t
JOIN OrderDetails o ON o.OrderId = t.Id;
-- Plan built for "1 row joining into OrderDetails" -> a nested loop
-- seek, which is disastrous once @OrderIds actually holds 50,000
-- values instead of the one the optimizer planned around.

-- Same query, temp table instead:
SELECT Id INTO #OrderIds FROM Orders WHERE Status = 'Pending';
-- #OrderIds DOES get real statistics, and the optimizer picks a
-- hash or merge join appropriately once the actual row count is
-- known at compile time.

✅ Fix: Use a Temp Table When the Row Count Isn’t Tiny and Predictable

• Prefer a temp table (#table) over a table variable specifically when the row count is unpredictable or likely to be large, since temp tables get real statistics the optimizer can actually use.
• When a table variable must be used (inside a function, for instance, where temp tables aren’t allowed), add OPTION (RECOMPILE) to the query that joins against it, forcing SQL Server to use the actual row count at execution time instead of its default one-row assumption.
• On SQL Server 2019 and later, table variable deferred compilation addresses part of this automatically – but confirm it against the actual execution plan rather than assuming the version alone fixes it, since behavior still varies by compatibility level and query shape.

⚠️ Why This Is Easy to Miss

• The query runs correctly and returns the right rows every single time – this is purely a performance problem, which means it often survives code review, QA, and even production for a while if the table variable’s row count stays small during early testing.
• Switching from a table variable to a temp table only helps if the statistics actually get used – a query wrapped in a scalar function, or one running under certain isolation or transaction contexts, can still behave unexpectedly, so confirming the actual execution plan after the change matters more than assuming the fix worked.