For years, SQL Server professionals are using Multi-Statement Table-Valued Functions (MSTVFs). The concept of „reusability“ of code originates from high-level programming languages and appears equally attractive for database projects. For this reason, Microsoft SQL Server has offered the option to use User-Defined Functions (UDFs) within a database. Unfortunately, using UDFs comes at a high cost. A drawback that Microsoft sought to mitigate with SQL Server 2017. Regardless of whether the function produced 10 rows or 10 million rows, SQL Server assumed a fixed cardinality estimate of 100 rows. This estimate was often highly inaccurate. The result was predictable: poor join strategies, insufficient memory grants, excessive I/O, and disappointing query performance.
Interleaved Execution
Microsoft recognized this problem and introduced Interleaved Execution in SQL Server 2017. At first glance, the feature looked like the long-awaited solution. Instead of blindly guessing the number of rows returned by an MSTVF, the optimizer could execute the function during compilation, observe the actual row count, and use that information to build a better execution plan.
Interleaved Execution Improves Estimates. The Parallelism Problem Remains!
While Interleaved Execution improves cardinality estimates, it does not change the underlying implementation of a Multi-Statement TVF. The function still populates and returns a table variable. However, that design choice introduces a limitation that Interleaved Execution cannot solve.: table variables are processed serially.
As a consequence, SQL Server can generate a parallel execution plan for operators outside the function, but the statements inside the MSTVF itself still execute using a single thread. The larger the workload inside the function becomes, the more this serial execution turns into a bottleneck.
Interleaved Execution makes SQL Server smarter about estimating MSTVF results. It does not make MSTVFs scalable.
Many DBAs see improved plans after upgrading to SQL Server 2017 or later and assume that mTVFs are no longer problematic. In reality, the optimizer has merely gained better visibility.The architectural limitation remains unchanged. Consequently, if the function performs significant joins, aggregations, or data transformations, the absence of parallelism can still severely restrict performance.
Better estimates are helpful. Parallel execution is sometimes essential. And a table variable output still prevents SQL Server from fully exploiting the available CPU resources.
Good Estimates, Bad Scalability – a practical Demo
At first glance, the following Multi-Statement Table-Valued Function (MSTVF) looks like an excellent candidate for SQL Server 2017’s Interleaved Execution feature.The function returns customer information for a specific nation. It performs multiple joins and executes a correlated aggregation through a CROSS APPLY. The resulting rows are stored in the table variable that forms the function’s return value. Prior to SQL Server 2017, the optimizer struggled to estimate the number of rows produced by the function. By contrast, Interleaved Execution allows SQL Server to observe the actual cardinality during optimization.
CREATE OR ALTER FUNCTION demo.get_customer_by_nation
(
@n_name CHAR(25)
)
RETURNS @t TABLE
(
c_custkey BIGINT NOT NULL ,
c_name VARCHAR(25) NOT NULL,
n_name CHAR(25) NOT NULL,
last_orderdate DATE NOT NULL,
num_of_orders BIGINT NOT NULL DEFAULT (0),
PRIMARY KEY CLUSTERED (c_custkey)
)
AS
BEGIN
INSERT INTO @t (c_custkey, c_name, n_name, last_orderdate, num_of_orders)
SELECT c.c_custkey,
c.c_name,
@n_name,
o.o_last_orderdate,
o.o_max_orders
FROM dbo.nations AS n
INNER JOIN dbo.customers AS c
ON (n.n_nationkey = c.c_nationkey)
CROSS APPLY
(
SELECT MAX(o.o_orderdate) AS o_last_orderdate,
COUNT_BIG(o.o_custkey) AS o_max_orders
FROM demo.orders AS o
WHERE o.o_custkey = c.c_custkey
) AS o
WHERE n.n_name = @n_name
AND o.o_last_orderdate IS NOT NULL
RETURN;
END
GO
To demonstrate Interleaved Execution, we’ll execute a query that joins the customers table with the MSTVF demo.get_customer_by_nation('Austria'). Prior to SQL Server 2017, the optimizer had to rely on fixed assumptions about the function’s cardinality, often leading to poor execution plans. With Interleaved Execution, SQL Server executes the MSTVF during optimization, captures the actual row count, and recompiles the remaining query using accurate cardinality information. As a result, the execution plan reflects realistic row estimates. At the same time, it allows us to focus on what Interleaved Execution solves and what it does not
/* The estimates will be correct with INTERLEAVED EXECUTION */
SELECT cbn.c_custkey,
cbn.c_name,
cbn.last_orderdate,
cbn.num_of_orders,
c.c_acctbal
FROM dbo.customers AS c
INNER JOIN demo.get_customer_by_nation('Austria') AS cbn
ON (c.c_custkey = cbn.c_custkey)
ORDER BY
num_of_orders DESC;
The execution plan demonstrates one of the key advantages introduced by Interleaved Execution in SQL Server 2017. Instead of relying on a fixed cardinality assumption for the Multi-Statement Table-Valued Function, SQL Server executes the function during the optimization phase and uses the actual row count to optimize the remainder of the query. In this example, the MSTVF returns 3,776 rows, and the optimizer correctly estimates 3,776 rows, resulting in a perfect estimate-to-actual ratio.
Peering Inside the MSTVF: What Query Store Reveals
One of the most valuable improvements introduced alongside Interleaved Execution is the ability to inspect the statements executed within a Multi-Statement TVF through Query Store. The function is no longer a complete black box. We can now examine the execution plan used to populate the table variable. This allows us to determine where SQL Server actually spends its time..
The Query Store plan provides the smoking gun that explains why this MSTVF cannot scale efficiently. The plan itself appears well optimized. However, the properties of the INSERT operator reveal a critical detail highlighted in the red box! This property tells us exactly why SQL Server did not even consider generating a parallel plan. The function returns its result through the table variable @t, and the final step of the execution is the insertion of rows into that table variable. Table variable transactions do not support the nested transaction semantics required for parallel inserts. As a result, SQL Server is forced to execute the entire operation using a single worker thread.
Better Optimizer Intelligence, Same Architectural Limitation
Microsoft deserves significant credit for the improvements made to Multi-Statement Table-Valued Functions in SQL Server 2017 and later. Interleaved Execution addresses one of the most frustrating aspects of MSTVFs by allowing the optimizer to observe the actual result set size before generating the final plan.
However, it is important to understand what Interleaved Execution does and does not solve. The feature improves the optimizer’s decision-making process, but it does not alter the internal implementation of a Multi-Statement TVF. The function still materializes its results in a table variable, and table variables continue to be subject to a fundamental engine limitation: Table Variable Transactions Do Not Support Parallel Nested Transactions. As a consequence, the workload required to populate the table variable remains serial, regardless of how accurate the cardinality estimates become.
This is not a criticism of MSTVFs alone. In reality, the root cause is the underlying behavior of table variables themselves. Since MSTVFs are built on top of a table variable return object, they inherit this limitation automatically. The result is that even modern versions of SQL Server cannot parallelize the work performed inside the function, potentially creating a scalability bottleneck on systems with many CPU cores and large data volumes.
For that reason, my recommendation remains unchanged: avoid Multi-Statement Table-Valued Functions whenever possible and prefer Inline Table-Valued Functions (iTVFs) instead. Inline functions are fully expanded into the calling query, participate naturally in query optimization, support parallel execution, and avoid the architectural restrictions imposed by table variables. While Interleaved Execution has made MSTVFs less problematic than they once were, it has not changed the fact that inline functions remain the most optimizer-friendly, scalable, and future-proof choice for SQL Server developers.
Thank you for reading!
