MSTVFs and table variables

In one line: multi-statement table-valued functions (MSTVFs) and table variables have no statistics, so for years the optimizer guessed their row counts at 1 or 100. SQL Server 2017 and 2019 replaced the guesses with real counts, but only in some situations.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/mstvf-table-variables on GitHub.

What you’ll see

  • A plan built for 1 or 100 rows that meets hundreds of thousands: nested loops, a seek per row, and a spilled sort. 199,697 rows through a 1-row plan cost 624,815 reads, against 28,852 with the right estimate.
  • A function that’s fast when tested in SSMS and slow in the ETL job.
  • A procedure fast on its first call and terrible on the next, with a different-sized table variable.

Why it happens

  • Interleaved execution (compatibility level 140+) runs an MSTVF first and compiles the rest with the real count, but only in statements that don’t modify data. As INSERT ... SELECT, the estimate went back to 100 and the reads to 743,861.
  • An MSTVF builds every row before you filter. A WHERE CustomerID = 42 outside the function still filled it with 500,000 rows to return 25.
  • Table variable deferred compilation (compatibility level 150+) gives a table variable a row count, not statistics. A filter on one of its columns is still a fixed guess (447 for 189,712 rows). The count is also cached with the plan: a plan compiled for 499 rows was reused for 199,697 and read 1.7 million pages.

What to do

  • Write inline table-valued functions (a single RETURN (SELECT ...)). They’re expanded into the query like a view and use the base tables’ statistics. The inline version read 9,172 pages against 28,852 for the interleaved MSTVF, and 29 against 35,023 for the filtered case.
  • Use a temp table instead of a table variable when its rows are filtered or joined on anything but the key, or when the row count varies between calls.
  • OPTION (RECOMPILE) on the statement that reads a table variable whose size varies.

What doesn’t help

  • Relying on interleaved execution in an INSERT ... SELECT. It doesn’t apply there.
  • Assuming deferred compilation fixes table variables. It fixes the count for one compile, not the column distribution.
  • Expecting a table variable to roll back. It isn’t part of the transaction. That’s a trap, and also the one thing it’s uniquely good for: keeping a log in a CATCH block after you roll back the work.

Check your server

-- Multi-statement table-valued functions in this database (candidates to rewrite as inline),
-- and the compatibility level that decides interleaved execution and deferred compilation.
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name                          AS function_name,
       o.type_desc,
       DbCompatLevel = (SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID())
FROM sys.objects AS o
WHERE o.type IN ('TF', 'IF')
ORDER BY o.type DESC, function_name;

SQL_TABLE_VALUED_FUNCTION is multi-statement. SQL_INLINE_TABLE_VALUED_FUNCTION is inline.

See it happen

The mstvf-table-variables demo measures the fixed guesses under each compatibility level, interleaved execution and where it stops, and deferred compilation and its cached count, against inline functions and temp tables.

Related: tempdb and #temp tables · scalar UDF inlining · parameter sniffing

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights