Multi-Statement Table-Valued Functions and Interleaved Execution

A function returns thousands of rows, but the joining query plans for a handful. Multi-statement table-valued functions hide their result size during ordinary optimization. Interleaved execution gives eligible queries a chance to see that size before the rest of the plan is finished.

Find the Fixed Guess in Multi-Statement Table-Valued Functions

A multi-statement table-valued function, or MSTVF, fills a declared table variable and returns it. The optimizer does not have normal column statistics on that result. SQL Server 2014 and later use a fixed 100-row estimate in the old behavior; earlier versions used one row. The estimate can be far from reality. A nested loops join chosen for 100 rows can be costly when the function returns many thousands.

I first inspect the actual plan at the function scan. Compare Estimated Number of Rows with Actual Number of Rows and look at the join and memory grant above it. If those values are close, the function's fixed guess is not the main problem. If they diverge sharply, the downstream plan deserves attention. Do not rewrite a function only because it has an unpopular acronym.

Create a Small Lab Function

Run the following in a disposable test database. It creates a function that returns object IDs from the local catalog. The parameter is a runtime value, which matters for interleaved execution eligibility. The GO separator keeps CREATE FUNCTION in its own batch. Change the function name if your test database already has an object with that name.

CREATE OR ALTER FUNCTION dbo.Demo_MSTVF_N01 (@MaximumID int)
RETURNS @Result TABLE (ObjectID int NOT NULL PRIMARY KEY)
AS
BEGIN
    INSERT @Result (ObjectID)
    SELECT object_id
    FROM sys.all_objects
    WHERE object_id <= @MaximumID;
    RETURN;
END;
GO
SELECT COUNT_BIG(*) AS joined_objects
FROM dbo.Demo_MSTVF_N01(2147483647) AS f
JOIN sys.all_objects AS o ON o.object_id = f.ObjectID;

Use an actual execution plan and note the function's row estimate, the rows returned, and the join selected. Because sys.all_objects includes system objects, even a new test database returns far more than 100 rows here. The point is the gap between the estimate and the result, not a particular elapsed time from this example.

Let the First Execution Inform the Plan

At compatibility level 140 or higher on a supporting engine, interleaved execution can pause optimization and materialize the MSTVF result. It captures the actual row count, then continues optimizing the outer statement. This changes the estimate used for downstream choices. It does not add full statistics on the returned columns. A join can still suffer from skew or correlations that a single row count cannot describe.

Inspect the actual plan properties. ContainsInterleavedExecutionCandidates on the QueryPlan node says a candidate exists. IsInterleavedExecuted on the TVF runtime information says the operation was used in an interleaved execution. An estimated plan alone cannot show the revised cardinality, because the function has not run. Capture the first execution and check the properties rather than assuming that compatibility level proves use.

Check the Switch Without Changing It

An eligible database can still have the feature disabled. Read the compatibility level and the available scoped configuration name. SQL Server 2017 uses a negative DISABLE_INTERLEAVED_EXECUTION_TVF setting; newer versions use INTERLEAVED_EXECUTION_TVF. The catalog query finds either form when present. Read its name with the value so a 1 is not mistaken for the opposite meaning.

SELECT d.name, d.compatibility_level
FROM sys.databases AS d
WHERE d.database_id = DB_ID();
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name LIKE N'%INTERLEAVED%';

Do not lower a production database's compatibility level merely to create a demonstration. That change affects many optimizer behaviors. Use a test copy or a query-level hint for a controlled comparison when available. Keep the query text and parameters the same. A different input size can make a different plan look like a feature effect.

When Multi-Statement Table-Valued Functions Are Not Eligible

The outer statement must be read-only, and the function call needs a runtime constant for this feature. An INSERT that consumes the function result does not receive the same interleaving behavior. A plain SELECT from the function with no meaningful downstream work also has little to gain. The feature helps when the revised row count changes a join, sort, or memory grant decision above the function.

Cached plans add another detail. The first execution can materialize the function during optimization and store the revised estimate. Later executions can reuse that plan without repeating the interleaved optimization step. If parameter values make the function return very different numbers of rows, a cached estimate from the first run can still be a poor fit. What range of result sizes does your application actually send through the function?

Compare Multi-Statement Table-Valued Functions With an Inline One

An inline table-valued function is a single SELECT expression. SQL Server can expand it into the outer query and reason about the base tables and their statistics. If the multi-statement function is really one relational query, an inline rewrite can be a better long-term fix than relying on a corrected row count alone. Test semantics carefully, especially around filters, duplicates, and ordering assumptions.

CREATE OR ALTER FUNCTION dbo.Demo_InlineTVF_N01 (@MaximumID int)
RETURNS TABLE
AS
RETURN
(
    SELECT object_id AS ObjectID
    FROM sys.all_objects
    WHERE object_id <= @MaximumID
);
GO
SELECT COUNT_BIG(*) AS joined_objects
FROM dbo.Demo_InlineTVF_N01(2147483647) AS f
JOIN sys.all_objects AS o ON o.object_id = f.ObjectID;

Compare results, logical reads, CPU, and plan shape. An inline rewrite can expose more choices to the optimizer, but it can also change a plan in ways that need testing. Keep the MSTVF if its imperative logic is genuinely required, and consider a temporary table with statistics for complex staged work. I favor the version that gives correct results and stable performance across representative parameter values.

Leave a Measurement Trail

Document the function result size, fixed or revised estimate, join choice, memory grant, and elapsed time for each test. If a plan changes after an upgrade, those numbers tell you whether interleaved execution helped. If a function returns only a few rows and the plan is already efficient, leave it alone. A feature is not a maintenance task merely because it appears in an upgrade guide.

After the lab, remove the demo functions from the test database if they are no longer needed. Do that as a deliberate cleanup step, not as part of a script someone can paste into the wrong database. The useful lesson is to read the function's row estimate and the work above it. The function name alone never tells the whole story.

Related reading on this blog: SQL SERVER 2019: Disabling Scalar UDF Inlining and Scalar Functions and Performance.

Interleaved execution is not a full rewrite, it is a better row estimate for the optimizer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.