A procedure fills a table variable with thousands of rows, yet the next join plans for one. Table variable deferred compilation in SQL Server 2019 lets the first execution supply the row count before that statement is compiled. The improvement is real, but it is not full statistics.
Life Before Table Variable Deferred Compilation
At compatibility level 140, a statement reading a table variable typically sees a one-row estimate. The variable has no ordinary column statistics to describe distribution. If it actually holds thousands of rows, a nested loops join or a small memory grant can be a poor choice. Check the actual plan's estimated and actual rows at the table variable scan before changing code.
I find this in procedures that use table variables because they look neat and easy to scope. The code can be correct while the plan is built for the wrong size. What row counts does the procedure place in the variable for typical and peak calls? One test with ten rows cannot answer for a batch that usually loads ten thousand.
Run a Controlled Level 140 Test
Use a disposable SQL Server 2019 or later database, not an application database. Compatibility changes affect many optimizer rules, so keep the experiment isolated. The script below switches the test database to 140, fills a table variable, and joins it to a catalog view. Capture the actual execution plan and STATISTICS IO. Replace the database name with your dedicated lab database.
ALTER DATABASE [LabDB] SET COMPATIBILITY_LEVEL = 140;
GO
USE [LabDB];
GO
DECLARE @IDs TABLE (ObjectID int NOT NULL);
INSERT @IDs (ObjectID)
SELECT TOP (10000) a.object_id
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
SELECT COUNT_BIG(*) AS matches_found
FROM @IDs AS i
JOIN sys.all_objects AS o ON o.object_id = i.ObjectID;The sample needs enough catalog rows to fill the variable. In a sparse test database, confirm the inserted count before interpreting the plan. The actual plan should show a small estimate for the table variable scan under the old behavior. Do not compare only total elapsed time; on a small catalog, both plans can finish quickly even with different estimates.
Repeat at Level 150
Switch only the lab database to 150 and rerun the same batch in a new session. At the first compilation of the reading statement, SQL Server can defer compilation until the table variable has rows. The plan can then use the actual row count from that first execution. Compare the scan estimate, join choice, memory grant, logical reads, and CPU with the level 140 result.
ALTER DATABASE [LabDB] SET COMPATIBILITY_LEVEL = 150;
GO
USE [LabDB];
GO
DECLARE @IDs TABLE (ObjectID int NOT NULL);
INSERT @IDs (ObjectID)
SELECT TOP (10000) a.object_id
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
SELECT COUNT_BIG(*) AS matches_found
FROM @IDs AS i
JOIN sys.all_objects AS o ON o.object_id = i.ObjectID;A table variable lives only for its batch, so rerunning the script causes no name collision. Compatibility changes also invalidate relevant plans, which helps the lab get a fresh compile. Keep data, query text, and session options consistent. If the two plans choose the same join, the feature can still have corrected the estimate. The plan is evidence; a promised speedup is not.
The First-Execution Limit of Table Variable Deferred Compilation
Deferred compilation sees the table variable row count at the first execution that compiles the statement. The resulting plan can be cached and reused. If a later call fills ten rows instead of ten thousand, the cached estimate can be a poor fit. It also does not provide a histogram for the variable's values. A correct total row count does not tell the optimizer whether one key dominates or whether two columns correlate.
I test small, ordinary, and large parameter values through the actual procedure. Query Store can show whether one plan serves them well. A query-level recompile can refresh the row count for each execution, but adds compile cost. Use it only after measuring the trade. Deferred compilation is a better starting estimate, not a guarantee of stable plans across every variable size.
Check Whether Table Variable Deferred Compilation Is Off
A database scoped configuration can turn table variable deferred compilation off while compatibility level remains 150 or higher. Inspect the setting rather than assuming level 150 proves it is active. Newer engine versions can offer additional related optimizations, so read the current database state when explaining a plan.
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = N'DEFERRED_COMPILATION_TV';
SELECT compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();An absent setting on an older engine is different from OFF. Do not change the scoped switch until you have a plan regression or a controlled test. If an old query improves after the level change, record the before and after plan IDs and runtime numbers, not just the database option.
When a Temporary Table Is Better
A temporary table can have statistics on its columns and can be indexed for the joins and filters that follow. It is a better choice when the intermediate result is large, highly skewed, reused across several statements, or needs more than a row-count estimate. The trade is tempdb work and explicit lifecycle management. A table variable can still be fine for small, predictable sets.
CREATE TABLE #IDs (ObjectID int NOT NULL);
INSERT #IDs (ObjectID)
SELECT TOP (10000) a.object_id
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
CREATE INDEX IX_IDs_ObjectID ON #IDs (ObjectID);
SELECT COUNT_BIG(*) AS matches_found
FROM #IDs AS i
JOIN sys.all_objects AS o ON o.object_id = i.ObjectID;The sample loads the same ten thousand rows as the table variable test, so the results are comparable. The syntax shows the temporary table's index option. Build the index after loading when that is cheaper for the real batch. If the intermediate table is read several times with different filters, add only the indexes that the measured downstream work repays. Compare result sets first, then plan estimates and measured cost. An index on an intermediate table is worthwhile only when its build cost is repaid by the following work.
Choose From the Real Procedure
Keep the table variable if its row count is stable and the plan is good. Use a temporary table when column distribution or indexing matters. Consider a targeted recompile when row counts swing widely and compilation cost is acceptable. Do not rewrite every table variable because one procedure improved after an upgrade.
I close the test with the inserted row counts, compatibility levels, scan estimates, plan shapes, and runtime metrics. That evidence explains why the newer behavior helped or did not. A one-row estimate is easy to spot, but the right replacement still depends on the rest of the query.
Related reading on this blog: Temp Table vs Table Variable: Cardinality Estimation and Table Variables, Temp Tables and Parallel Queries.
Deferred compilation is not column statistics, it is a better first row-count estimate.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
