User-Defined Functions in SQL Server

A user-defined function (UDF) in SQL Server is a reusable routine that takes parameters, runs T-SQL, and returns either a single value or a table. SQL Server 2022 supports three kinds: scalar functions that return one value, inline table-valued functions (iTVF) that return the result of a single SELECT, and multi-statement table-valued functions (mTVF) that build up a returned table variable. This article shows how to write and call each one, explains determinism and schemabinding, lists the restrictions, and covers the scalar UDF inlining feature added in SQL Server 2019 that changes how you should think about performance.

Scalar functions: return a single value

A scalar UDF returns one value of a specified type. It is handy for encapsulating a calculation you repeat across queries — tax, a formatted name, a business rule.

CREATE OR ALTER FUNCTION dbo.GetLineTotal
(
    @Quantity  INT,
    @UnitPrice DECIMAL(10,2),
    @TaxRate   DECIMAL(5,4)
)
RETURNS DECIMAL(12,2)
AS
BEGIN
    RETURN (@Quantity * @UnitPrice) * (1 + @TaxRate);
END;

Call it anywhere an expression is allowed. Schema-qualify the name (dbo.) — this is required for scalar UDFs:

SELECT OrderId,
       dbo.GetLineTotal(Quantity, UnitPrice, 0.08) AS LineTotal
FROM dbo.OrderLines;

Scalar UDFs are attractive because they keep business logic in one place. If the tax rule changes, you edit one function instead of hunting down the same arithmetic scattered across dozens of queries and reports. They also make queries read more like the domain they model.

The catch is performance. Historically a scalar UDF called in a SELECT list or WHERE clause ran once per row, was invisible to the optimizer, and forced row-by-row execution. On large sets this is often the single biggest reason a query is slow. Filtering on a scalar UDF in a WHERE clause is especially costly because it also prevents the engine from using an index seek on the underlying column. Keep this in mind — we return to how SQL Server 2019 and 2022 soften this problem at the end of the article.

Inline table-valued functions: a parameterized view

An inline TVF (iTVF) has no BEGIN/END body. It is a single RETURN SELECT, so SQL Server expands it directly into the calling query — exactly like a view that accepts parameters. This gives the optimizer full visibility and, in practice, the best performance of the three kinds.

CREATE OR ALTER FUNCTION dbo.OrdersForCustomer
(
    @CustomerId INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderId, OrderDate, Total
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
);

Because it returns a table, you call it in the FROM clause, and you can join to it and filter it like any other table source:

SELECT c.Name, o.OrderId, o.Total
FROM dbo.Customers AS c
CROSS APPLY dbo.OrdersForCustomer(c.CustomerId) AS o
WHERE o.Total > 500;

CROSS APPLY invokes the function once per customer row and joins the results, which is a common and efficient pattern for iTVFs. Notice that the WHERE o.Total > 500 filter is not trapped inside the function — because the iTVF is expanded into the outer query, the optimizer can push that predicate down and combine it with whatever indexes exist on dbo.Orders. This transparency is exactly why an inline TVF usually outperforms both a scalar UDF and a multi-statement TVF for set-based work.

Inline table-valued function returning orders for a customer

Multi-statement table-valued functions and why they can be slower

An mTVF declares a table variable, runs one or more statements to populate it, then returns it. Use this only when the logic genuinely needs multiple steps that a single SELECT can't express.

CREATE OR ALTER FUNCTION dbo.CustomerOrderSummary
(
    @CustomerId INT
)
RETURNS @Result TABLE
(
    OrderYear   INT,
    OrderCount  INT,
    TotalAmount DECIMAL(14,2)
)
AS
BEGIN
    INSERT INTO @Result (OrderYear, OrderCount, TotalAmount)
    SELECT YEAR(OrderDate), COUNT(*), SUM(Total)
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    GROUP BY YEAR(OrderDate);

    RETURN;
END;

You call an mTVF the same way as an iTVF, in the FROM clause. The difference is under the hood: the optimizer cannot see inside the function body, so it estimates the returned table using a fixed row guess (100 rows on the compatibility levels used by SQL Server 2022, 1 row on older levels). A bad estimate leads to poor join strategies and memory grants. SQL Server 2017 and later can partly mitigate this with interleaved execution, which pauses to sample the function's actual output before finalizing the plan, but that only applies in specific cases. As a rule, prefer an inline TVF over a multi-statement TVF whenever the logic can be rewritten as a single query. The CustomerOrderSummary example above is a good candidate: its single grouped SELECT could be an inline TVF, and rewriting it that way would remove the estimation problem entirely.

Determinism and schemabinding

A function is deterministic if it always returns the same output for the same inputs — GetLineTotal above is deterministic, while anything using GETDATE() or NEWID() is not. Determinism matters when you want to index a computed column or view that references the function.

Adding WITH SCHEMABINDING binds the function to the schema of the objects it references, so those objects can't be altered or dropped out from under it. It also lets SQL Server reason more accurately about the function and is a prerequisite for some indexing scenarios:

CREATE OR ALTER FUNCTION dbo.GetLineTotal
(
    @Quantity INT, @UnitPrice DECIMAL(10,2), @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(12,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN (@Quantity * @UnitPrice) * (1 + @TaxRate);
END;

What UDFs are not allowed to do

Functions must be free of side effects. Inside any UDF you cannot:

Perform INSERT, UPDATE, or DELETE against base tables (only a table variable local to an mTVF may be modified).
Execute dynamic SQL or call a stored procedure that has side effects.
Use TRY...CATCH, THROW for structured error handling, or transactions.
Call non-deterministic, state-changing functions in a way that affects the database.

If you need side effects, use a stored procedure instead. These restrictions exist so that a function stays a pure computation the query engine can call safely, in any order, as many times as it likes.

Scalar UDF inlining in SQL Server 2019 and 2022

SQL Server 2019 introduced scalar UDF inlining, part of Intelligent Query Processing and still in effect in SQL Server 2022. When a scalar UDF meets the eligibility rules, the engine automatically rewrites it into an equivalent relational expression that gets folded into the calling query — removing the per-row invocation and giving the optimizer a real cost estimate. Many previously slow scalar UDFs now run dramatically faster with no code change, as long as the database is on a modern compatibility level (150 or higher).

Inlining is not guaranteed: functions using time-dependent functions, certain aggregates, or recursion may be ineligible, and you can check with the is_inlineable column in sys.sql_modules. The practical guidance still holds — for anything returning a set, prefer an inline TVF, and reserve scalar UDFs for genuinely scalar results, leaning on inlining to keep them cheap.

Key takeaways

SQL Server 2022 has three UDF kinds: scalar (one value), inline TVF (one RETURN SELECT), and multi-statement TVF (a populated table variable).
Inline TVFs behave like parameterized views and give the optimizer full visibility, so they are the fastest table-returning option.
Multi-statement TVFs are opaque to the optimizer and use a fixed cardinality guess, which can cause bad plans on larger result sets.
Use WITH SCHEMABINDING for stability and better estimates; determinism is what enables indexing scenarios.
UDFs cannot have side effects — no DML on base tables, no transactions — use stored procedures for that.
Scalar UDF inlining (SQL Server 2019+, compatibility level 150+) speeds up many scalar UDFs automatically; still prefer inline TVFs for set-based logic.

Frequently asked questions

What is the difference between an inline and a multi-statement table-valued function?

An inline TVF is a single RETURN SELECT with no body; SQL Server expands it into the calling query like a view, so the optimizer sees the real logic. A multi-statement TVF fills a declared table variable across several statements, and the optimizer treats it as an opaque source with a fixed row estimate.

Why are scalar UDFs considered slow in SQL Server?

Before SQL Server 2019, a scalar UDF ran once per row and was invisible to the optimizer, forcing row-by-row execution. Scalar UDF inlining in SQL Server 2019 and 2022 removes this for eligible functions by rewriting them into inlined relational expressions.

Can a user-defined function modify data in a table?

No. UDFs cannot run INSERT, UPDATE, or DELETE against base tables or perform any other side effect. Only the table variable local to a multi-statement TVF may be written to. Use a stored procedure when you need to change data.

When should I use SCHEMABINDING on a function?

Use WITH SCHEMABINDING when you want to prevent referenced objects from being altered or dropped, when you need the function in an indexed view or persisted computed column, or simply to help SQL Server reason about the function more accurately.