skip to content

In SQL Server, how do scalar, inline table-valued and multi-statement table-valued functions differ in definition and in how the optimizer treats them?

level: middleimportance: must knowfreq 64%

answer

  1. one value or one table
  2. a single SELECT with no body
  3. a table variable filled and returned
  4. a fixed row guess
  5. interleaved execution since 2017

basics

~20 s

A scalar function returns one value per call. An inline table-valued function is a single SELECT that SQL Server expands into the calling query like a view. A multi-statement function fills a table variable the optimizer guesses at 100 rows.

solid answer

~50 s

SQL Server has three T-SQL function types. A **scalar** function returns one value from a `BEGIN...END` body. Before SQL Server 2019 it ran once per qualifying row in a serial plan. From 2019, inlining can turn eligible ones into expressions. An **inline table-valued function** is `RETURNS TABLE AS RETURN (SELECT ...)`. It has no body, so the optimizer expands it into the calling query like a parameterized view and sees the real tables, indexes and statistics. A **multi-statement table-valued function** declares `RETURNS @t TABLE (...)`, inserts rows in a body and returns. The result is materialized, and the optimizer uses a fixed guess of 100 rows (1 row before SQL Server 2014). Interleaved execution, from SQL Server 2017, can use the real count for eligible read-only queries. So I prefer inline TVFs, and I add `SCHEMABINDING` so referenced objects can't change underneath a function.

code

sql · 31 lines
sql
CREATE TABLE dbo.Invoices (
    InvoiceId  INT IDENTITY PRIMARY KEY,
    CustomerId INT NOT NULL,
    Amount     DECIMAL(12, 2) NOT NULL
);
GO
-- Inline TVF: one SELECT, expanded into the calling query
CREATE OR ALTER FUNCTION dbo.tvf_CustomerInvoices (@CustomerId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT InvoiceId, Amount
    FROM dbo.Invoices
    WHERE CustomerId = @CustomerId;
GO
-- Multi-statement TVF: fills a table variable, then returns it
CREATE OR ALTER FUNCTION dbo.mstvf_CustomerInvoices (@CustomerId INT)
RETURNS @result TABLE (InvoiceId INT, Amount DECIMAL(12, 2))
WITH SCHEMABINDING
AS
BEGIN
    INSERT INTO @result (InvoiceId, Amount)
    SELECT InvoiceId, Amount
    FROM dbo.Invoices
    WHERE CustomerId = @CustomerId;
    RETURN;
END;
GO
SELECT * FROM dbo.tvf_CustomerInvoices(42);
SELECT * FROM dbo.mstvf_CustomerInvoices(42);

go deeper

for a junior

Recall the three types: a scalar function returns one value, an inline TVF is a single SELECT, and a multi-statement TVF fills and returns a table variable.

for a middle

Explain why the inline TVF is expanded like a view while the MSTVF is materialized with a fixed estimate. Name 100 rows since 2014, 1 before, and interleaved execution from 2017.

for a senior

Diagnose the classic regression: an actual-versus-estimated gap on an MSTVF scan driving a bad join. Fix it by rewriting the function as an inline TVF, and add SCHEMABINDING where it applies.

for a principal

Set a team rule for which function types are allowed in hot query paths, and when a review should ask for a rewrite to inline form.

## The three T-SQL function types A **user-defined function (UDF)** in SQL Server is a module created with `CREATE FUNCTION` that takes parameters and returns either a single value or a table. T-SQL offers three shapes: | Type | Declared as | Body | How the optimizer sees it | |---|---|---|---| | Scalar | `RETURNS int` (any scalar type) | `BEGIN ... RETURN expr END` | Invoked per row unless inlined (2019 and later, when eligible) | | Inline table-valued (inline TVF) | `RETURNS TABLE` | None: `AS RETURN (single SELECT)` | Expanded into the calling query like a parameterized view | | Multi-statement table-valued (MSTVF) | `RETURNS @t TABLE (columns)` | `BEGIN ... INSERT @t ...; RETURN; END` | Materialized result with a fixed row estimate | All three can be called from `SELECT`. Scalar functions also appear in computed columns and `CHECK` constraints, and must be called by at least a two-part name such as `dbo.fn_Tax(...)`. Table-valued functions go in a `FROM` clause. ## Scalar functions A scalar UDF returns one value. Historically it was slow for structural reasons: - **Per-row invocation.** It runs once per qualifying row, with a context switch each time. - **No costing.** The optimizer does not cost the statements inside the function. - **Serial plans.** A query that calls a non-inlined T-SQL scalar UDF cannot use intra-query parallelism. SQL Server 2019 added **scalar UDF inlining** under database compatibility level 150 and later. Eligible functions are rewritten into expressions or subqueries inside the calling query. Eligibility, and how to check it, is a separate topic. For the comparison here, a scalar UDF is the one type whose performance depends heavily on the release and the function's contents. ## Inline table-valued functions An inline TVF contains exactly one `SELECT`, with no `BEGIN...END`. Because there is no procedural body, SQL Server substitutes the query text into the caller, much as it does with a view. As a result: 1. The optimizer sees the **base tables**, so it can use their indexes and statistics and push predicates down. 2. The estimate is a **real cardinality estimate**, not a guess. 3. The `is_inlineable` column of `sys.sql_modules` reports `1` for every inline TVF. Restrictions follow from being a single statement: no variables or control flow, and `EXECUTE AS` cannot be specified for an inline TVF. ## Multi-statement table-valued functions An MSTVF declares a return **table variable**, fills it with any number of statements, and returns it. The engine runs the body and stores the rows before the outer query can use them. The optimizer historically knew nothing about how many rows would come back: - From SQL Server 2014 onward, an MSTVF gets a **fixed cardinality guess of 100 rows**. Earlier releases guessed **1 row**. - When the function actually returns hundreds of thousands of rows, that guess leads to plans such as nested loops sized for 100 rows. - **Interleaved execution**, available from SQL Server 2017 (compatibility level 140), pauses optimization, runs the MSTVF, and resumes with the actual row count. It applies only to read-only statements that are not part of a data modification, and only when the MSTVF uses runtime constants. A plain `SELECT *` from the function gains nothing from it. The standing advice in the documentation is to join base tables rather than functions where possible. When an MSTVF can be expressed as one `SELECT`, rewriting it as an inline TVF gives the optimizer a real estimate instead of a guess. ## Rules that apply to every UDF - A function **cannot change database state**. It may modify only its own local table variables. - No dynamic SQL, no temporary tables (table variables are allowed), and no calls to stored procedures (extended procedures are allowed). - No `TRY...CATCH`, `RAISERROR` or `@@ERROR`, and no `SET` statements other than variable assignment. - Functions nest up to **32 levels**. ## SCHEMABINDING `WITH SCHEMABINDING` binds a function to the objects it references. Those objects cannot then be altered in a way that would affect the function until the function is changed or dropped. It also matters to the optimizer: - SQL Server determines a T-SQL function's determinism and data-access properties itself, and **a function must be schema-bound to be deterministic**. - For a UDF that **does not access data**, the documentation recommends `SCHEMABINDING` so the optimizer does not add unnecessary spool operators to plans that call it. Without it, an unbound function can silently go stale when a referenced table changes. Running `sp_refreshsqlmodule` after such changes is the alternative.

  • In SQL Server, when does interleaved execution not help a multi-statement table-valued function?
    It applies only from SQL Server 2017 at compatibility level 140 or later, and only to read-only statements that are not part of a data modification. The MSTVF must also use runtime constants. A plain `SELECT *` from the function gains nothing, because no downstream decision depends on the estimate. It can be switched off with the `INTERLEAVED_EXECUTION_TVF` database scoped configuration or a `USE HINT`.
  • What does SCHEMABINDING prevent on a SQL Server function?
    Changes to the referenced objects that would affect the function's definition. You cannot drop or alter a referenced column or table until the function is altered without `SCHEMABINDING` or dropped. That stops the function going stale. It is also required for SQL Server to treat a T-SQL function as deterministic. For functions that read no data, it lets the optimizer skip unnecessary spools.

  • An inline TVF behaves like a multi-statement TVF: both materialize their rows first
  • The optimizer estimates a multi-statement TVF's rows from statistics on its table variable
  • Scalar UDFs have always been inlined into the calling query
  • A function may update a table as long as it is called from a procedure
  • SCHEMABINDING is required for scalar UDF inlining

Assumes SQL Server 2025; the 100-row MSTVF guess dates from 2014, interleaved execution from 2017 (compatibility level 140) and scalar UDF inlining from 2019 (compatibility level 150).