Scalar UDFs vs Inline TVFs: Measuring the Damage

2026-07-27 · by Hannah Vernon · in performance

Scalar user-defined functions are the most natural thing in the world to write. You have a bit of logic, you wrap it in a function, you call it in your SELECT list, and your query is suddenly clean and readable and forty times slower. That last part tends to arrive later, on someone else’s on-call shift.

Split illustration comparing thousands of tiny workers carrying single data cubes through a narrow door versus one wide conveyor belt carrying all the cubes at once

This post takes one piece of logic and implements it three ways: as a classic scalar UDF, as the same scalar UDF with SQL Server 2019’s automatic inlining allowed to work, and as an inline table-valued function. Then it measures all three, on SQL Server 2019 (CU32) and SQL Server 2025 (RTM), with the full demo scripts so you can reproduce every number.

The setup

One table, a million rows, and three functions that all compute the same thing: the age of an order in days, floored at zero.

USE [master];

IF DB_ID(N'tvf_demo') IS NULL
BEGIN
    CREATE DATABASE [tvf_demo];
END;
GO

USE [tvf_demo];
GO

CREATE TABLE [dbo].[orders]
(
      [order_id]    int      NOT NULL IDENTITY(1, 1)
    , [order_date]  datetime NOT NULL
    , [amount]      money    NOT NULL
    , CONSTRAINT [pk_orders] PRIMARY KEY CLUSTERED ([order_id])
);
GO

/* 1,000,000 orders with pseudo-random dates */
INSERT INTO [dbo].[orders]
(
      [order_date]
    , [amount]
)
SELECT TOP (1000000)
      DATEADD(MINUTE, 0 - (ABS(CHECKSUM(NEWID())) % 5256000), CONVERT(datetime, N'2026-01-01T00:00:00', 126))
    , CONVERT(money, (ABS(CHECKSUM(NEWID())) % 100000)) / 100.0
FROM
    sys.all_columns AS [ac1]
    CROSS JOIN sys.all_columns AS [ac2];

USE [master];

IF DB_ID(N'tvf_demo') IS NULL

BEGIN

CREATE DATABASE [tvf_demo];

END;

USE [tvf_demo];

CREATE TABLE [dbo].[orders]

(

[order_id] int NOT NULL IDENTITY(1, 1)

, [order_date] datetime NOT NULL

, [amount] money NOT NULL

, CONSTRAINT [pk_orders] PRIMARY KEY CLUSTERED ([order_id])

);

/* 1,000,000 orders with pseudo-random dates */

INSERT INTO [dbo].[orders]

(

[order_date]

, [amount]

)

SELECT TOP (1000000)

DATEADD(MINUTE, 0 - (ABS(CHECKSUM(NEWID())) % 5256000), CONVERT(datetime, N'2026-01-01T00:00:00', 126))

, CONVERT(money, (ABS(CHECKSUM(NEWID())) % 100000)) / 100.0

FROM

sys.all_columns AS [ac1]

CROSS JOIN sys.all_columns AS [ac2];

The three contestants. First, the classic scalar UDF. I am explicitly disabling inlining with WITH INLINE = OFF so it behaves the way every scalar UDF behaved before SQL Server 2019:

CREATE OR ALTER FUNCTION [dbo].[order_age_days_scalar]
(
    @order_date datetime
)
RETURNS int
WITH INLINE = OFF
AS
BEGIN
    DECLARE @age int;

    SET @age = DATEDIFF(DAY, @order_date, CONVERT(datetime, N'2026-01-01T00:00:00', 126));

    IF @age < 0
    BEGIN
        SET @age = 0;
    END;

    RETURN @age;
END;

CREATE OR ALTER FUNCTION [dbo].[order_age_days_scalar]

(

@order_date datetime

)

RETURNS int

WITH INLINE = OFF

BEGIN

DECLARE @age int;

SET @age = DATEDIFF(DAY, @order_date, CONVERT(datetime, N'2026-01-01T00:00:00', 126));

IF @age < 0

BEGIN

SET @age = 0;

END;

RETURN @age;

END;

Second, the identical function with the default inlining behavior (no INLINE clause, so SQL Server decides):

CREATE OR ALTER FUNCTION [dbo].[order_age_days_inlineable]
(
    @order_date datetime
)
RETURNS int
AS
BEGIN
    DECLARE @age int;

    SET @age = DATEDIFF(DAY, @order_date, CONVERT(datetime, N'2026-01-01T00:00:00', 126));

    IF @age < 0
    BEGIN
        SET @age = 0;
    END;

    RETURN @age;
END;

CREATE OR ALTER FUNCTION [dbo].[order_age_days_inlineable]

(

@order_date datetime

)

RETURNS int

BEGIN

DECLARE @age int;

SET @age = DATEDIFF(DAY, @order_date, CONVERT(datetime, N'2026-01-01T00:00:00', 126));

IF @age < 0

BEGIN

SET @age = 0;

END;

RETURN @age;

END;

Third, the same logic as an inline table-valued function. Note there is no BEGIN...END body at all; an iTVF is a single RETURN of a query, which is precisely why the optimizer can treat it like a view and fold it into the calling query:

CREATE OR ALTER FUNCTION [dbo].[order_age_days_itvf]
(
    @order_date datetime
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        [age_days] = CASE
                         WHEN DATEDIFF(DAY, @order_date, CONVERT(datetime, N'2026-01-01T00:00:00', 126)) < 0
                             THEN 0
                         ELSE DATEDIFF(DAY, @order_date, CONVERT(datetime, N'2026-01-01T00:00:00', 126))
                     END
);

CREATE OR ALTER FUNCTION [dbo].[order_age_days_itvf]

(

@order_date datetime

)

RETURNS TABLE

RETURN

(

SELECT

[age_days] = CASE

WHEN DATEDIFF(DAY, @order_date, CONVERT(datetime, N'2026-01-01T00:00:00', 126)) < 0

THEN 0

ELSE DATEDIFF(DAY, @order_date, CONVERT(datetime, N'2026-01-01T00:00:00', 126))

END

);

The race

Each variant aggregates over the full million rows, so the function logic runs a million times (or, spoiler, gets rewritten so that it does not):

USE [tvf_demo];

SET STATISTICS TIME ON;

/* scalar UDF, INLINE = OFF */
SELECT
    [buckets] = COUNT(DISTINCT [dbo].[order_age_days_scalar]([o].[order_date]))
FROM
    [dbo].[orders] AS [o];

/* scalar UDF, inlineable */
SELECT
    [buckets] = COUNT(DISTINCT [dbo].[order_age_days_inlineable]([o].[order_date]))
FROM
    [dbo].[orders] AS [o];

/* inline TVF via CROSS APPLY */
SELECT
    [buckets] = COUNT(DISTINCT [a].[age_days])
FROM
    [dbo].[orders] AS [o]
    CROSS APPLY [dbo].[order_age_days_itvf]([o].[order_date]) AS [a];

SET STATISTICS TIME OFF;

USE [tvf_demo];

SET STATISTICS TIME ON;

/* scalar UDF, INLINE = OFF */

SELECT

[buckets] = COUNT(DISTINCT [dbo].[order_age_days_scalar]([o].[order_date]))

FROM

[dbo].[orders] AS [o];

/* scalar UDF, inlineable */

SELECT

[buckets] = COUNT(DISTINCT [dbo].[order_age_days_inlineable]([o].[order_date]))

FROM

[dbo].[orders] AS [o];

/* inline TVF via CROSS APPLY */

SELECT

[buckets] = COUNT(DISTINCT [a].[age_days])

FROM

[dbo].[orders] AS [o]

CROSS APPLY [dbo].[order_age_days_itvf]([o].[order_date]) AS [a];

SET STATISTICS TIME OFF;

Warm-cache results, second run, on both instances:

                              SQL Server 2019 (CU32)      SQL Server 2025 (RTM)
                              CPU ms      elapsed ms      CPU ms      elapsed ms
scalar UDF (INLINE = OFF)      3,109         3,874         4,734         5,501
scalar UDF (inlined)             469            79           295            97
inline TVF (CROSS APPLY)          94            83            78            31

SQL Server 2019 (CU32) SQL Server 2025 (RTM)

CPU ms elapsed ms CPU ms elapsed ms

scalar UDF (INLINE = OFF) 3,109 3,874 4,734 5,501

scalar UDF (inlined) 469 79 295 97

inline TVF (CROSS APPLY) 94 83 78 31

Same logic, same rows, same instance: the pre-2019-style scalar UDF takes about 4 seconds, and both modern shapes take well under 100 milliseconds. The gap is not the arithmetic; DATEDIFF costs next to nothing. The gap is a million function invocations, each with its own T-SQL execution context, each a black box the optimizer cannot cost, running on a plan that scalar UDFs force to be serial. The inlined variants get rewritten into relational expressions inside the calling query, where the optimizer can vectorize the work across the scan and, when useful, parallelize it.

The gotcha I hit so you don’t have to

My first benchmark run on the 2019 instance produced a surprise: the “inlineable” scalar UDF ran just as slowly as the INLINE = OFF one. Four seconds, serial plan, no inlining.

The cause: my demo database had inherited compatibility level 130 from the instance’s model database. Scalar UDF inlining is part of the Intelligent Query Processing family and requires compatibility level 150 or higher^[1]. The feature does not warn you; the function simply executes the old way. You can check whether SQL Server considers a function inlineable, and whether your database will actually do it:

SELECT
      [fn]            = OBJECT_NAME([sm].[object_id])
    , [is_inlineable] = [sm].[inline_type]
FROM
    [sys].[sql_modules] AS [sm]
WHERE
    [sm].[object_id] = OBJECT_ID(N'dbo.order_age_days_inlineable');

SELECT
    [compat] = [d].[compatibility_level]
FROM
    [sys].[databases] AS [d]
WHERE
    [d].[name] = DB_NAME();

SELECT

[fn] = OBJECT_NAME([sm].[object_id])

, [is_inlineable] = [sm].[inline_type]

FROM

[sys].[sql_modules] AS [sm]

WHERE

[sm].[object_id] = OBJECT_ID(N'dbo.order_age_days_inlineable');

SELECT

[compat] = [d].[compatibility_level]

FROM

[sys].[databases] AS [d]

WHERE

[d].[name] = DB_NAME();

inline_type = 1 plus compatibility level 150+ gets you inlining. Either one missing gets you 2017 performance in a 2025 instance. If you are carrying old compatibility levels around, I wrote about upgrading compatibility levels across a whole instance, and this feature is one of the better reasons to do it.

So which one should you write?

Inlining is impressive, and the numbers above show it working almost as well as the hand-written iTVF. But I still reach for the inline TVF when the logic is destined for set-based use, for three reasons:

Inlining has a long list of disqualifiers. GETDATE() and other time-dependent intrinsics, security functions, table variable access, recursion, WITH SCHEMABINDING in some shapes, and more^[1]. Functions drift over time; one innocent edit can silently turn inlining off, and nothing tells you. An iTVF cannot lose a property it never depended on.
The iTVF is honest about being a query. The Froid framework behind inlining^[2] works by translating imperative function bodies into relational expressions. An iTVF skips the translation because you wrote the relational expression yourself.
It works everywhere. Compatibility level 100 or 170, Standard or Enterprise, the iTVF behaves the same.

The CROSS APPLY calling pattern takes a day to get used to, and then it is simply how you write it. If the apply operator itself is unfamiliar territory, I recently dug into one of its more expensive side effects in finding eager index spools in the plan cache.

For a deeper practical treatment of both function types, Erik Darling has excellent dedicated lessons on scalar UDFs^[3] and inline table-valued functions^[4], both with video.

Clean up when you are done:

USE [master];

DROP DATABASE IF EXISTS [tvf_demo];

USE [master];

DROP DATABASE IF EXISTS [tvf_demo];

Have a scalar UDF horror story, or a function that mysteriously refuses to inline? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.

References

Scalar UDF Inlining – Microsoft Learn. Requirements, the full list of inlining disqualifiers, and the inline_type / WITH INLINE controls. ↩
Froid: Optimization of Imperative Programs in a Relational Database – Ramachandra et al., PVLDB Vol. 11. The research behind scalar UDF inlining: how imperative function bodies are translated into relational algebra. ↩
Learn T-SQL With Erik: Scalar UDFs – Erik Darling, erikdarling.com. Why scalar UDFs hurt, demonstrated, with video. ↩
Learn T-SQL With Erik: Inline Table Valued Functions – Erik Darling, erikdarling.com. The iTVF pattern done properly. ↩

Tags: performance, SQL Server, SQL Server 2025, t-sql, user-defined functions

Scalar UDFs vs Inline TVFs: Measuring the Damage

The setup

The race

The gotcha I hit so you don’t have to

So which one should you write?

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Scalar UDFs vs Inline TVFs: Measuring the Damage

The setup

The race

The gotcha I hit so you don’t have to

So which one should you write?

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta