Functions and Triggers: Scalar UDF Inlining, Inline TVFs, Multi-Statement TVFs and Trigger Pitfalls

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0.5035.5, Cumulative Update 10); for the comparisons the database's compatibility level was switched between 130, 140 and 170.

Guess First: How Many Times Does This Function Run?

The Bookstore has a small scalar function that calculates an order's total. It was written to avoid repeating code and is used everywhere in reports:

CREATE OR ALTER FUNCTION dbo.OrderTotal (@OrderId int)
RETURNS decimal(12,2)
AS
BEGIN
    RETURN (SELECT SUM(Quantity * UnitPrice) FROM dbo.OrderItems WHERE OrderId = @OrderId);
END;
GO
SET STATISTICS IO ON;
SELECT COUNT(*) AS orders, SUM(dbo.OrderTotal(o.OrderId)) AS revenue
FROM dbo.Orders AS o
WHERE o.CustomerId = 2;

Customer 2 has 17,359 orders. The database is at compatibility level 140 (SQL Server 2017 behavior). How many times does the function run, and how many logical reads do you see in the STATISTICS IO output?

orders|revenue
------|-------
17359|1370933.10
Table 'Orders'. Scan count 1, logical reads 34, physical reads 0, ...

function_name|execution_count|total_logical_reads
-------------|---------------|-------------------
OrderTotal|34718|365498

STATISTICS IO shows only the 34 pages of Orders. But sys.dm_exec_function_stats says the function ran 34,718 times and did 365,498 logical reads: less than a thousandth of the query's real cost is visible. On top of that, 34,718 is exactly twice the number of orders. This post explains with real measurements why scalar functions behave like this, the inlining that came with SQL Server 2019, the two kinds of table-valued functions, and the pitfalls of triggers.

The Hidden Cost of a Scalar UDF

Below compatibility level 150, a T-SQL scalar UDF (User-Defined Function) is a separate module called once per row. The optimizer doesn't see inside it; every call sets up its own context, runs its own query and returns the result. You can see this in the plan:

  |--Stream Aggregate(DEFINE:([Expr1003]=Count(*), [Expr1004]=COUNT_BIG([sqls_15].[dbo].[OrderTotal]([sqls_15].[dbo].[Orders].[OrderId] as [o].[OrderId])), [Expr1005]=SUM([sqls_15].[dbo].[OrderTotal]([sqls_15].[dbo].[Orders].[OrderId] as [o].[OrderId]))))
       |--Index Seek(OBJECT:([sqls_15].[dbo].[Orders].[IX_Orders_CustomerId] AS [o]), SEEK:([o].[CustomerId]=(2)) ORDERED FORWARD)

The reason for the double execution is right here: to tell an empty set from NULL, SUM also computes a COUNT_BIG internally, and the function expression is evaluated separately in the two aggregates. Because the optimizer doesn't know how expensive the function is, it doesn't see a problem with that.

There are three more costs. The reads of the query inside the function don't show up in STATISTICS IO, so you misdiagnose "this query reads 34 pages". The function's own plan is compiled separately and doesn't take part in the outer query's estimates. And a query containing a T-SQL scalar UDF can't use a parallel plan: this plan ran on a single thread.

Scalar UDF Inlining

SQL Server 2019 brought scalar UDF inlining with compatibility level 150: the body of an eligible function is embedded into the calling query as an expression or subquery, and the optimizer optimizes the whole thing as one query. The same query at compatibility level 170:

Table 'OrderItems'. Scan count 17359, logical reads 182749, physical reads 0, ...
Table 'Orders'. Scan count 5, logical reads 36, physical reads 0, ...

function_name|execution_count|total_logical_reads
-------------|---------------|-------------------

  |--Stream Aggregate(DEFINE:([globalagg1014]=SUM([partialagg1013]), ...))
       |--Parallelism(Gather Streams)
            |--Stream Aggregate(DEFINE:([partialagg1013]=Count(*), ...))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([o].[OrderId]))
                      |--Index Seek(OBJECT:([sqls_15].[dbo].[Orders].[IX_Orders_CustomerId] AS [o]), SEEK:([o].[CustomerId]=(2)) ORDERED FORWARD)
                      |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1019]=(0) THEN NULL ELSE [Expr1020] END))
                           |--Stream Aggregate(DEFINE:([Expr1019]=COUNT_BIG([Expr1008]), [Expr1020]=SUM([Expr1008])))
                                |--Index Seek(OBJECT:([sqls_15].[dbo].[OrderItems].[IX_OrderItems_OrderId]), ...)

Four things changed. There is no row in the function statistics, because the function is no longer called. The reads of OrderItems show up in STATISTICS IO: 182,749, half of the old hidden cost, because the expression is now evaluated once. The plan is parallel. And the aggregate from inside the function has moved into the outer query's nested loops: the optimizer now sees the whole thing.

Not every function can be inlined. sys.sql_modules.is_inlineable tells you which ones qualify:

CREATE OR ALTER FUNCTION dbo.OrderAgeDays (@CreatedAt datetime2(0))
RETURNS int
AS
BEGIN
    RETURN DATEDIFF(DAY, @CreatedAt, SYSUTCDATETIME());
END;
GO
SELECT OBJECT_NAME(object_id) AS function_name, is_inlineable, inline_type
FROM sys.sql_modules
WHERE object_id IN (OBJECT_ID('dbo.OrderTotal'), OBJECT_ID('dbo.OrderAgeDays'));

function_name|is_inlineable|inline_type
-------------|-------------|-----------
OrderTotal|1|1
OrderAgeDays|0|0

The simple-looking OrderAgeDays can't be inlined because it calls a time-dependent intrinsic function, SYSUTCDATETIME(). Other blockers in the documentation include intrinsic functions with side effects, using table variables or table-valued parameters, and EXECUTE AS; the list gets updated with every Cumulative Update, so don't guess, check is_inlineable. You can turn inlining off per function with WITH INLINE = OFF, per query with OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING')), and per database with ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF.

Inlining doesn't always mean fewer reads; in our example the reads halved because the double evaluation went away, but otherwise the plan still does one seek per order. The best result usually comes from not using the function at all and writing a set-based JOIN and GROUP BY. Inlining is a safety net for old code you can't change.

Inline TVF: A View With Parameters

There are two kinds of table-valued functions, and although their names are similar, they are completely different for the optimizer. An inline TVF (Table-Valued Function) consists of a single SELECT:

CREATE OR ALTER FUNCTION dbo.CustomerOrdersInline (@CustomerId int)
RETURNS TABLE
AS
RETURN
    SELECT OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE CustomerId = @CustomerId;

The optimizer expands it like a view: the function's query is placed into the calling query, and statistics and indexes are used as usual. "A view that takes parameters" is the most accurate description. You can use it safely to share repeated filters and projections; the predicate functions of Row-Level Security are of this kind too (post 17).

Multi-Statement TVFs and Interleaved Execution

A multi-statement TVF (MSTVF), on the other hand, is a small program that fills and returns a table variable:

CREATE OR ALTER FUNCTION dbo.CustomerOrdersMulti (@CustomerId int)
RETURNS @result TABLE (OrderId int PRIMARY KEY, CreatedAt datetime2(0), TotalAmount decimal(12,2))
AS
BEGIN
    INSERT @result (OrderId, CreatedAt, TotalAmount)
    SELECT OrderId, CreatedAt, TotalAmount FROM dbo.Orders WHERE CustomerId = @CustomerId;
    RETURN;
END;

We use the two functions in the same query, joined to the order lines, and read the estimated and actual row counts from the actual plan (SET STATISTICS XML ON):

SELECT COUNT(*) AS lines, SUM(i.Quantity) AS books
FROM dbo.CustomerOrdersMulti(2) AS co
JOIN dbo.OrderItems AS i ON i.OrderId = co.OrderId;

function                      compat  estimated rows  actual rows  join          OrderItems logical reads
CustomerOrdersMulti           130     100             17359        Nested Loops  188756
CustomerOrdersMulti           170     17359           17359        Hash Match    9964
CustomerOrdersInline          130     17359           17359        Hash Match    9964
CustomerOrdersInline          170     17359           17359        Hash Match    9964

At compatibility level 130 the estimate for an MSTVF is fixed: 100 rows. For 100 rows the optimizer chose a plan with nested loops and key lookups; in reality 17,359 rows came back and OrderItems was read with 188,756 logical reads. The inline TVF, in the same database, went to a hash join with the right estimate: 9,964 reads, about 19 times fewer.

From compatibility level 140 onward, interleaved execution kicks in: when the optimizer reaches the MSTVF it pauses optimization, runs the function, learns the real row count and compiles the rest of the plan accordingly. In the plan XML (Extensible Markup Language) you see this as ContainsInterleavedExecutionCandidates="1" and as IsInterleavedExecuted="1" on the TVF operator. The result is the same plan as the inline TVF. But it has limits: it works only in SELECT statements and only at first compilation; the plan in the plan cache keeps the row count from the first call. The plan compiled for customer 2 is also used for customer 15018, who has a single order. And the table variable returned by an MSTVF has no statistics; it carries no distribution information beyond the row count. If you have the choice, write an inline TVF.

Triggers: inserted, deleted and the Single-Row Assumption

In the Bookstore, a trigger reserves stock when an order line is inserted. The first version was tested with rows inserted one at a time:

CREATE OR ALTER TRIGGER dbo.OrderItems_ReserveStock
ON dbo.OrderItems
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @BookId int, @Quantity smallint;
    SELECT @BookId = BookId, @Quantity = Quantity FROM inserted;
    UPDATE dbo.StockItems SET QuantityReserved += @Quantity WHERE BookId = @BookId;
END;
GO
INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice)
VALUES (999001, 10, 1, 9.99), (999001, 11, 2, 9.99), (999001, 12, 3, 9.99);
SELECT BookId, QuantityReserved FROM dbo.StockItems WHERE BookId IN (10, 11, 12);

BookId|QuantityReserved
------|----------------
10|1
11|0
12|0

Three lines were inserted, only one was reserved. A trigger runs once per statement, not once per row; inserted (and deleted in UPDATE/DELETE) are pseudo tables containing all the rows affected by that statement. Assigning to a variable takes one of the rows (which one is not guaranteed) and silently drops the rest. Because Entity Framework Core (EF Core) can send several rows in a single statement in SaveChanges, in a real application this bug shows up with the first multi-line order. The right way is set-based:

CREATE OR ALTER TRIGGER dbo.OrderItems_ReserveStock
ON dbo.OrderItems
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM inserted) RETURN;

    UPDATE s
    SET QuantityReserved += i.Quantity
    FROM dbo.StockItems AS s
    JOIN (SELECT BookId, SUM(Quantity) AS Quantity FROM inserted GROUP BY BookId) AS i
        ON i.BookId = s.BookId;
END;

BookId|QuantityReserved
------|----------------
10|1
11|2
12|3

The GROUP BY also sums correctly when the same book comes twice in the same statement. IF NOT EXISTS is a reminder that the trigger runs for statements that affect zero rows too.

Triggers have another side effect that breaks applications:

INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice)
OUTPUT inserted.OrderItemId
VALUES (999002, 13, 1, 9.99);

Msg 334, Level 16, State 1, Server b8be943222fa, Line 1
The target table 'dbo.OrderItems' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Since EF Core 7, the SQL Server provider reads new identity values exactly like this, with an OUTPUT without INTO. If you write to a table with triggers through EF Core, you need to mark the table in the model with ToTable(t => t.HasTrigger("OrderItems_ReserveStock")); EF Core then generates slower but trigger-compatible SQL for that table.

AFTER triggers run after the DML (Data Manipulation Language) statement completes, inside the same transaction; an error in the trigger rolls back the whole statement. INSTEAD OF triggers run in place of the statement and are mostly used to write to views that can't be updated otherwise. A trigger can fire the trigger of another table by writing to it: the nested triggers server setting is 1 by default (up to 32 levels). A table's trigger firing itself depends on the RECURSIVE_TRIGGERS database option, which is off by default; we saw both at their defaults in the lab.

Instead of Triggers for Auditing

The most common use of triggers is recording "who changed what and when". SQL Server's built-in tools make fewer mistakes for that. A temporal table automatically keeps every version of the table in a history table:

ALTER TABLE dbo.Books ADD
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
        CONSTRAINT DF_Books_ValidFrom DEFAULT SYSUTCDATETIME(),
    ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
        CONSTRAINT DF_Books_ValidTo DEFAULT CONVERT(datetime2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE dbo.Books SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.BooksHistory));
GO
UPDATE dbo.Books SET Price = 12.50 WHERE BookId = 1;
UPDATE dbo.Books SET Price = 11.90 WHERE BookId = 1;
GO
SELECT BookId, Price, ValidFrom, ValidTo
FROM dbo.Books FOR SYSTEM_TIME ALL
WHERE BookId = 1
ORDER BY ValidFrom;

BookId|Price|ValidFrom|ValidTo
------|-----|---------|-------
1|32.77|2026-11-08 12:12:39.2073938|2026-11-08 12:12:39.2622087
1|12.50|2026-11-08 12:12:39.2622087|2026-11-08 12:12:39.2675017
1|11.90|2026-11-08 12:12:39.2675017|9999-12-31 23:59:59.9999999

The price history is kept without writing a single line of code; with FOR SYSTEM_TIME AS OF you can query the price at any moment. HIDDEN period columns don't show up in SELECT *, so existing queries and ORM (Object-Relational Mapping) mappings don't break. What's missing is the "who"; a temporal table doesn't store the user, so you need to add an UpdatedBy column to the table for that.

Other options: Change Tracking records which row changed (not the value) in a lightweight way and suits synchronization scenarios. CDC (Change Data Capture) reads the transaction log and writes the changed values to separate tables; it's used to feed event streams and data warehouses. At application level, EF Core interceptors, as we saw in the interceptors post of the EF Core series, fill audit columns and user information during SaveChanges. The rule: don't bury business logic in triggers. When a business rule such as stock reservation lives in a trigger, someone reading the code doesn't know it exists; it should be visible in a procedure or in the application.

What's Different in SQL Server 2019/2022

Scalar UDF inlining came with SQL Server 2019 at compatibility level 150; it works the same in 2019 and 2022, but the inlining rules were narrowed and fixed through Cumulative Updates, so the same function can have is_inlineable = 1 on one version and 0 on another. In a database running at compatibility level 140 or lower, even on SQL Server 2022, you'll see the 34,718 calls from the first example. Interleaved execution has existed since 2017 (compatibility level 140). Temporal tables have existed since 2016. Triggers and the OUTPUT restriction haven't changed between versions.

Watch Out in Production

Measure the hidden cost. sys.dm_exec_function_stats shows the most called and most reading functions; if a query's STATISTICS IO is low but its duration is long, that's the first place to look. In Query Store the statements of a scalar UDF show up as separate queries.

Check functions when you raise the compatibility level. Inlining changes plans; rarely, you may see a worse plan or a different result (for example an error inside the function now surfaces in a different order). First list is_inlineable, then compare critical reports with Query Store (post 19).

Take an inventory of triggers. Query sys.triggers and test what each trigger does and whether it works correctly with multi-row DML. Triggers come with surprises such as EF Core's OUTPUT restriction and @@ROWCOUNT being affected by statements inside the trigger.

Keep triggers short. A trigger runs in the transaction of the statement that fired it; every read and write in it extends the lock time of the user's INSERT. Long work (e-mail, external systems) isn't done in a trigger but through a queue.

Common Pitfalls

Putting a scalar UDF in the WHERE. WHERE dbo.OrderAgeDays(CreatedAt) > 30 both calls the function for every row and makes the index on CreatedAt unusable. WHERE CreatedAt < DATEADD(DAY, -30, SYSUTCDATETIME()) does the same job with a seek.

Choosing an MSTVF because it's "more flexible". Most MSTVFs that seem to need several statements can be turned into a single SELECT, that is an inline TVF, with UNION ALL, CASE and a CTE (Common Table Expression).

Reading a single row from inserted in a trigger. The most common mistake in this post; in code review, every SELECT @x = ... FROM inserted inside a trigger is a warning sign.

Filling an audit trigger with the connection instead of the user. If the application connects with a single SQL user, SUSER_SNAME() always returns the same name. You need to write the real user through SESSION_CONTEXT or on the application side.

A Small Experiment

★☆☆ Recreate dbo.OrderTotal with WITH INLINE = OFF and run the first query at compatibility level 170. What's the execution_count? Is the plan parallel?

★★☆ Write the first query without the function, joining Orders and OrderItems with GROUP BY. Compare its STATISTICS IO with the inlined version.

★★★ Run dbo.CustomerOrdersMulti first with CustomerId = 15018 (one order), then with CustomerId = 2, without clearing the plan cache. What's the estimate on the second call? Explain the consequence of interleaved execution being tied to the first compilation.

We touched the estimation problem of table variables through MSTVFs. In the next post we go down into tempdb itself: temp tables, table variables and CTEs, which one when, table variable deferred compilation, and tempdb contention.