SQL Server User-Defined Functions: Scalar vs Table-Valued, Inlining and Performance

User-defined functions (UDFs) let you package T-SQL logic and reuse it in queries, the way functions work in any programming language. That convenience has a well-known cost in SQL Server: depending on the function type and version, a function can force a query to run serially, hide its real cost from the execution plan, and run once for every row. The same logic written as a different kind of function can be hundreds of times faster.

This guide explains the three types of user-defined functions, how each one is executed, what scalar UDF inlining in SQL Server 2019 and later changes, how to find expensive functions, and when to use a function, a view or a stored procedure instead.

Built-in and User-Defined Functions

SQL Server has hundreds of built-in functions: string functions such as CONCAT and REGEXP_LIKE, date functions such as DATEADD, aggregates such as SUM, and window functions such as ROW_NUMBER. They’re implemented inside the engine and are generally cheap.

User-defined functions are T-SQL (or CLR) code you write yourself. There are three kinds, and the difference between them matters far more than the syntax suggests:

Scalar Functions: The Classic Performance Trap

CREATE OR ALTER FUNCTION dbo.fn_CustomerTotal (@CustomerID int)
RETURNS decimal(18,2)
AS
BEGIN
    RETURN (SELECT SUM(Amount) FROM dbo.Orders WHERE CustomerID = @CustomerID);
END;
GO

SELECT CustomerID, Name, dbo.fn_CustomerTotal(CustomerID) AS total
FROM dbo.Customers;

Without inlining, this query runs the function’s SELECT once for every customer, as a separate execution. On top of that, a non-inlined scalar UDF:

• Forces a serial plan for the whole query, even if the rest of it would benefit from parallelism.
• Hides its cost: the execution plan shows the function as a cheap Compute Scalar, and the I/O inside the function doesn’t appear in the query’s statistics.
• Gets no cardinality estimate for its internal query in the context of the caller.

Scalar UDF inlining in SQL Server 2019 and later

At compatibility level 150 and above, SQL Server can automatically transform eligible scalar UDFs into relational expressions inside the calling query. The function body is optimized together with the query, parallelism becomes possible and per-row invocation disappears. Gains of an order of magnitude or more are common.

Not every function qualifies. A function isn’t inlined, among other reasons, if it calls time-dependent or side-effecting functions such as GETDATE() or NEWID(), uses table variables, is used in a computed column or CHECK constraint, or the calling query uses it in certain clauses. Check eligibility instead of guessing:

SELECT OBJECT_SCHEMA_NAME(m.object_id) + '.' + OBJECT_NAME(m.object_id) AS function_name,
       m.is_inlineable,
       m.inline_type
FROM sys.sql_modules AS m
JOIN sys.objects     AS o ON o.object_id = m.object_id
WHERE o.type = 'FN';   -- scalar functions

Rewriting a scalar UDF as an inline TVF

On older versions, or for functions that can’t be inlined, the most reliable fix is to rewrite the scalar function as an inline TVF and call it with CROSS APPLY (or OUTER APPLY when it may return no row):

CREATE OR ALTER FUNCTION dbo.itvf_CustomerTotal (@CustomerID int)
RETURNS TABLE
AS
RETURN (SELECT SUM(Amount) AS total FROM dbo.Orders WHERE CustomerID = @CustomerID);
GO

SELECT c.CustomerID, c.Name, t.total
FROM dbo.Customers AS c
OUTER APPLY dbo.itvf_CustomerTotal(c.CustomerID) AS t;

The optimizer now sees one query. It can choose a single scan of Orders with a hash aggregate instead of one lookup per customer, and it can run in parallel.

Inline Table-Valued Functions: The Fast Kind

An inline TVF is a single RETURN (SELECT ...) with no BEGIN ... END block. SQL Server expands it into the calling query exactly like a view that takes parameters, so indexes, statistics and parallelism all work normally. If you need reusable logic that returns rows, this is the type to choose.

Multi-Statement Table-Valued Functions

A multi-statement TVF declares a table variable, fills it with one or more statements and returns it. Because the result is a table variable built at run time, the optimizer has to guess how many rows it will return: historically a fixed estimate of 1 row, and 100 rows from SQL Server 2014’s cardinality estimator. Joins planned for 100 rows behave badly when the function returns a million.

From compatibility level 140, interleaved execution runs eligible multi-statement TVFs first and uses the actual row count to optimize the rest of the query. It helps, but an inline TVF is still the better design when the logic fits in one query.

Functions in WHERE Clauses

Any function applied to a column in a WHERE clause or JOIN condition, user-defined or built-in, usually prevents an index seek on that column, because SQL Server has to calculate the function for every row before it can compare:

-- Can't seek on OrderDate: the function runs for every row
WHERE dbo.fn_FiscalYear(OrderDate) = 2026

-- Sargable: compute the range once, compare the column directly
WHERE OrderDate >= '20250701' AND OrderDate < '20260701'

Apply functions to parameters and constants, not to columns. Our index guide lists the other common non-sargable patterns.

What Functions Can’t Do

Functions are meant to be free of side effects. Inside a user-defined function you can’t:

• Modify tables other than a table variable declared in the function (no INSERT, UPDATE or DELETE on real tables).
• Run dynamic SQL with EXEC or call stored procedures (except extended procedures that don’t change state).
• Use TRY…CATCH, create temporary tables or change SET options.
• Call nondeterministic functions such as NEWID() or RAND() directly.

WITH SCHEMABINDING is worth adding to most functions. It protects them from changes to the objects they reference, and for scalar functions that don’t access data it lets SQL Server mark them as deterministic and data-free, which removes unnecessary protective operators from update plans.

Function, View or Stored Procedure?

Finding Expensive Functions

sys.dm_exec_function_stats (SQL Server 2016 and later) shows how often each cached scalar function ran and how much CPU and I/O it used. A high execution count in a function is the signature of per-row invocation:

SELECT TOP (20)
    DB_NAME(fs.database_id)                        AS database_name,
    OBJECT_NAME(fs.object_id, fs.database_id)      AS function_name,
    fs.execution_count,
    fs.total_worker_time / 1000                    AS total_cpu_ms,
    fs.total_logical_reads,
    fs.total_worker_time / NULLIF(fs.execution_count, 0) AS avg_cpu_us
FROM sys.dm_exec_function_stats AS fs
ORDER BY fs.total_worker_time DESC;

Function Checklist

• Scalar UDFs are inlineable (is_inlineable = 1) on compatibility level 150 or higher, or rewritten as inline TVFs.
• Multi-statement TVFs are replaced with inline TVFs where the logic allows.
• No functions are applied to indexed columns in WHERE and JOIN conditions.
• Functions use WITH SCHEMABINDING.
• sys.dm_exec_function_stats is checked for functions with very high execution counts.
• Logic that modifies data lives in stored procedures, not functions.