Multi-Statement Table-Valued Functions in SQL Server: When You Need Procedural Logic

Sometimes a single SELECT genuinely can’t express what you need — the previous lesson’s exact limitation. That’s what multi-statement TVFs (mTVFs) are for, at a real, measurable cost that’s worth understanding before reaching for one.

Writing One

CREATE FUNCTION dbo.GetSalaryBands ()
RETURNS @Bands TABLE (
    band_name NVARCHAR(20),
    employee_count INT,
    avg_salary DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @Bands
    SELECT
        CASE WHEN salary < 65000 THEN 'Junior'
             WHEN salary BETWEEN 65000 AND 85000 THEN 'Mid'
             ELSE 'Senior' END,
        COUNT(*), AVG(salary)
    FROM dbo.Employee
    GROUP BY CASE WHEN salary < 65000 THEN 'Junior'
                  WHEN salary BETWEEN 65000 AND 85000 THEN 'Mid'
                  ELSE 'Senior' END;
    RETURN;
END;
GO

SELECT * FROM dbo.GetSalaryBands();

Notice the return type: RETURNS @Bands TABLE (...) defines an explicit table-variable shape, populated with ordinary INSERT statements inside a BEGIN/END body — fundamentally different from an iTVF's single implicit RETURN (SELECT ...). This structure is exactly what buys you procedural freedom (multiple statements, variables, even loops) at the cost described below.

The Real Cost

mTVFs lose the inlining benefit of an iTVF entirely — SQL Server treats the whole thing as a black box with a fixed row-count estimate (historically 1 row on older versions, 100 on newer defaults, version-dependent), regardless of how many rows the function actually returns. If a query joins the result of an mTVF to a large table, the optimizer's wildly wrong row estimate can lead it to choose a bad join strategy — a nested loop where a hash join would've been dramatically faster, for instance — for reasons that have nothing to do with your indexes or statistics being stale.

-- Confirm the black-box estimate yourself: enable the actual execution plan
-- and hover over the mTVF call in the plan — the Estimated Number of Rows
-- will not match reality, even though this specific function returns exactly 3 rows.
SELECT * FROM dbo.GetSalaryBands();

The Rule of Thumb

Prefer an iTVF whenever a single SELECT can express the logic. Reach for an mTVF only when you genuinely need procedural steps — and even then, seriously consider whether a stored procedure is actually the better fit, since mTVFs can't be indexed, can't be updated through, and carry the poor cardinality estimation shown above. A stored procedure returning a result set has none of these specific limitations, at the cost of not being directly usable inside a larger SELECT/JOIN the way a table-valued function is.