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.

Inline Table-Valued Functions in SQL Server: Parameterized Views That Actually Perform

An inline TVF (iTVF) is essentially a parameterized view — a single SELECT statement wrapped in a function, returning a table. Unlike scalar functions, these get inlined into the calling query’s execution plan, which is why this lesson comes with none of the previous one’s performance warnings.

Writing and Using One

CREATE FUNCTION dbo.GetEmployeesByDepartment (@dept NVARCHAR(50))
RETURNS TABLE
AS
RETURN (
    SELECT employee_id, first_name, last_name, salary
    FROM dbo.Employee
    WHERE department = @dept
);
GO

SELECT * FROM dbo.GetEmployeesByDepartment('Engineering');

-- Can be joined just like a table
SELECT e.first_name, e.salary
FROM dbo.GetEmployeesByDepartment('Sales') e
WHERE e.salary > 60000;

-- Can even be CROSS APPLY'd per row of another table (covered fully in Chapter 6)
SELECT d.department_name, top_earner.first_name
FROM dbo.Department d
CROSS APPLY (SELECT TOP 1 * FROM dbo.GetEmployeesByDepartment(d.department_name) ORDER BY salary DESC) top_earner;

That last example is worth pausing on: an iTVF can take a value from the outer query as its parameter, once combined with CROSS APPLY — something a plain view can never do, since a view has no parameters at all. This is one of the most useful, distinctly-SQL-Server patterns in the whole language.

Why iTVFs Perform Well

Because SQL Server can see straight into an iTVF’s single SELECT, it substitutes the function call with the equivalent SQL directly — conceptually similar to how a compiler inlines a small function — and produces accurate row estimates, exactly as if you’d hand-written the JOIN yourself. No separate execution step, no performance penalty, no black-box row-count guessing.

Proving the Inlining, Not Just Trusting It

-- Compare the execution plan of the function call...
SELECT * FROM dbo.GetEmployeesByDepartment('Engineering') WHERE salary > 70000;

-- ...against the hand-written equivalent. In SSMS, enable "Include Actual Execution Plan"
-- (Ctrl+M) for both and compare — for a true iTVF, the plans are identical.
SELECT employee_id, first_name, last_name, salary
FROM dbo.Employee WHERE department = 'Engineering' AND salary > 70000;

The One Real Limitation

An iTVF’s body must be exactly one SELECT statement — no variables, no IF/ELSE, no intermediate steps. The moment your logic needs a second statement (populate a temp result, then filter it based on something computed in step one), you’ve outgrown an iTVF and need either a multi-statement TVF (next lesson) or a restructured single query using a CTE, which often accomplishes the same goal without leaving iTVF territory at all.