T-SQL Functions: What the Optimizer Sees and What It Doesn’t

Three kinds of functions, three contracts with the optimizer

Built-in functions such as DATEADD or COALESCE do not belong on this list. They are part of the language and not an optimization boundary, but applied to a search column they can make an index unusable.

The scalar function: one call per row

Without inlining, SQL Server runs a scalar function for every row for which it needs the value. If the function itself reads data, every call runs a small query of its own. Three things make this expensive:

• The optimizer does not know the cost. In the estimated plan, the call appears as a cheap computation step. The work inside the function does not feed into plan choice.
• The whole plan runs serially. A T-SQL scalar function without inlining prevents parallelism for the entire query. In the actual plan, NonParallelPlanReason shows TSQLUserDefinedFunctionsNotParallelizable on SQL Server 2022 and 2025. In the lab, SQL Server 2017 and 2019 only gave the general reason CouldNotGenerateValidParallelPlan.
• The work is not batched. Instead of reading all lines once and grouping them, each execution looks up its own lines.

In the lab, a query calculated the net total per customer for the orders in a date range, in five ways. The result was the same every time; the code and the runtime were not. The tabs show the code, and below them is the result on SQL Server 2022 with 100,000 orders and 400,000 order lines:

SELECT o.CustomerId,       CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))            AS decimal(19,2)) AS NettoFROM dbo.Orders AS oJOIN dbo.OrderLines AS ol ON ol.OrderId = o.OrderId   -- all lines at onceWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

CREATE OR ALTER FUNCTION dbo.fn_OrderNetto (@OrderId int)RETURNS decimal(19,2)ASBEGIN    RETURN (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                        AS decimal(19,2))            FROM dbo.OrderLines AS ol            WHERE ol.OrderId = @OrderId);END;GO SELECT o.CustomerId,       SUM(dbo.fn_OrderNetto(o.OrderId)) AS Netto   -- one call per orderFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

-- Function and query are unchanged.-- Only the compatibility level (150+) and this option (default: ON) differ:ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON; SELECT o.CustomerId,       SUM(dbo.fn_OrderNetto(o.OrderId)) AS Netto   -- gets inlinedFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

CREATE OR ALTER FUNCTION dbo.itvf_OrderNetto (@OrderId int)RETURNS TABLE   -- a single SELECT, no BEGIN … ENDASRETURN (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                    AS decimal(19,2)) AS Netto        FROM dbo.OrderLines AS ol        WHERE ol.OrderId = @OrderId);GO SELECT o.CustomerId, SUM(n.Netto) AS NettoFROM dbo.Orders AS oCROSS APPLY dbo.itvf_OrderNetto(o.OrderId) AS nWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

CREATE OR ALTER FUNCTION dbo.mstvf_OrderNetto (@OrderId int)RETURNS @t TABLE (Netto decimal(19,2))   -- returned via a table variableASBEGIN    INSERT @t    SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                AS decimal(19,2))    FROM dbo.OrderLines AS ol    WHERE ol.OrderId = @OrderId;    RETURN;END;GO SELECT o.CustomerId, SUM(t.Netto) AS NettoFROM dbo.Orders AS oCROSS APPLY dbo.mstvf_OrderNetto(o.OrderId) AS tWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

The relational query and the scalar function without inlining on all four versions:

The relational query ran in parallel on 8 cores on all versions, the function serially. With 1,000 orders, it was 11–12 ms against 44–62 ms; with a single order, there was no measurable difference. So the function is not slow in itself. What gets expensive is its call per row once many rows need it. We did not investigate why the function ran slower from SQL Server 2019 on than on 2017.

Scalar UDF Inlining: SQL Server 2019 and later

Since SQL Server 2019, the optimizer can build many scalar functions into the calling query. The function call becomes part of the query, which it can estimate, rearrange, and parallelize. The prerequisites are compatibility level 150 or higher, the database option TSQL_SCALAR_UDF_INLINING turned on (the default), and a function that meets Microsoft's conditions.

In the lab, the same query also ran with inlining turned on, on SQL Server 2019, 2022, and 2025:

Inlining wins back almost all of the time. The plan no longer contains a function call; instead it shows ContainsInlineScalarTsqlUdfs=“1”, and it runs in parallel again. The inlined plan is not the relational one, though: it looks up the lines for each order individually instead of reading them once and grouping. That costs 20 times as many reads and 1.4 to 1.9 times the CPU. It did not show in the runtime because the work was spread across 8 cores. So inlined means the optimizer sees the logic. It does not mean the result is as cheap as a hand-written query.

Inlinable is not inlined

sys.sql_modules.is_inlineable = 1 only says that the function itself meets the conditions. Microsoft explicitly separates conditions on the function definition from conditions on the calling query. is_inlineable only takes the first kind into account. Whether a particular call gets inlined also depends on the call, the compatibility level, and the configuration. And a small change to the function can prevent inlining without changing anything about the result.

In the lab, the same function was given three forms and called in three contexts, each time for 100,000 orders with inlining turned on, on SQL Server 2019, 2022, and 2025. The result was the same in all cases. This is what the variants looked like:

CREATE OR ALTER FUNCTION dbo.fn_OrderNetto (@OrderId int)RETURNS decimal(19,2)ASBEGIN    RETURN (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                        AS decimal(19,2))            FROM dbo.OrderLines AS ol            WHERE ol.OrderId = @OrderId);   -- exactly one RETURNEND;

CREATE OR ALTER FUNCTION dbo.fn_OrderNetto_c (@OrderId int)RETURNS decimal(19,2)ASBEGIN    DECLARE @n decimal(19,2);    SET @n = (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                          AS decimal(19,2))              FROM dbo.OrderLines AS ol              WHERE ol.OrderId = @OrderId);    RETURN @n;   -- still only one RETURNEND;

CREATE OR ALTER FUNCTION dbo.fn_OrderNetto_b (@OrderId int)RETURNS decimal(19,2)ASBEGIN    IF @OrderId IS NULL    BEGIN        RETURN NULL;   -- second RETURN: no longer inlinable    END    RETURN (SELECT CAST(SUM(ROUND(ol.Qty * ol.Price * (1 - ol.Discount), 2))                        AS decimal(19,2))            FROM dbo.OrderLines AS ol            WHERE ol.OrderId = @OrderId);END;

SELECT dbo.fn_OrderNetto(o.OrderId) AS Netto, COUNT(*) AS AnzahlFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY dbo.fn_OrderNetto(o.OrderId);   -- not inlined

SELECT o.OrderId, o.CustomerIdFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'ORDER BY dbo.fn_OrderNetto(o.OrderId), o.OrderId;   -- not inlined

In summary:

Two findings:

• A harmless line costs you inlining. Since SQL Server 2019 CU5, only a function with exactly one RETURN qualifies. The early exit for NULL changes nothing about the result, but the runtime rises to eight times as much. Local variables, on the other hand, were no obstacle.
• Inlinable does not mean inlined. The same function with is_inlineable = 1 was not inlined in the GROUP BY and the ORDER BY. Microsoft lists both cases under the conditions on the calling query. So the column says whether a function can be inlined. Whether it was is shown only by the plan.

How to check: in the actual execution plan of an inlined function, the function call is missing; instead, the tables the function reads appear. The inventory on the overview page also shows which scalar functions actually ran as a call of their own. In the lab, exactly the function that had not been inlined showed up there, including in the cases with is_inlineable = 1. The number of calls is not always the number of rows: with 1,000 orders, SQL Server counted 2,000 calls because the plan used the function in two places.

Since 2019, Microsoft has fixed several bugs in inlining through cumulative updates and tightened the conditions in the process. After a CU update, check not only the runtime but also whether the function is still inlined. You can turn inlining off selectively per function (WITH INLINE = OFF), per query (OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))), or for the database.

The inline table-valued function: encapsulation without a black box

An inline table-valued function consists of exactly one SELECT statement. The optimizer builds it into the calling query like a view that you can pass parameters to. So it encapsulates the logic without hiding it, and it has done so in every version since SQL Server 2000.

In the lab, the inline TVF was on par with the relational query on all four versions, including SQL Server 2017, which has no inlining of scalar functions yet. With 100,000 orders, it took 0.71–0.81 s against 0.70–0.81 s, with the same roughly 16,250 reads and a parallel plan. The optimizer had resolved it completely.

Converting a scalar function to an inline TVF changes the call: the function call moves from the column list to the FROM clause.

SELECT o.CustomerId,       SUM(dbo.fn_OrderNetto(o.OrderId)) AS Netto   -- function in the column listFROM dbo.Orders AS oWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

SELECT o.CustomerId,       SUM(n.Netto) AS NettoFROM dbo.Orders AS oCROSS APPLY dbo.itvf_OrderNetto(o.OrderId) AS n   -- function in the FROM clauseWHERE o.OrderDate >= '2024-02-01' AND o.OrderDate < '2024-04-14'GROUP BY o.CustomerId;

This is more of a rewrite than a switch, but it also works on versions before 2019 and does not depend on inlining conditions. If the function returns NULL for an order without lines, CROSS APPLY behaves the same way here, because an aggregation without GROUP BY always returns one row.

The multi-statement table-valued function: the estimated table

A multi-statement table-valued function fills a table variable with several statements and returns it. The optimizer does not see what happens inside, only the table at the end. It does not know its size before execution:

• With the legacy cardinality estimator (before SQL Server 2014), it estimates 1 row; with the new one from 2014 on, 100 rows, regardless of how many there actually are.
• From SQL Server 2017 (compatibility level 140), interleaved execution can apply: the optimizer runs the function first, counts the rows, and optimizes the rest of the query with that number.
• That number is stored with the plan. If the next execution calls the function with a parameter that returns 100,000 rows instead of 10, the first count stays.

In the comparison of function kinds above, the multi-statement variant of the same calculation was the slowest: 8.7–11.1 s for 100,000 orders, serial. The function ran 100,000 times, with 100 rows estimated per call and one returned each time.

For the estimate itself, a second multi-statement function returned all order lines of a customer. Three customers with 10, 1,000, and 100,000 lines were queried one after another via sp_executesql, that is, with the same plan:

CREATE OR ALTER FUNCTION dbo.mstvf_KundenPositionen (@CustomerId int)RETURNS @t TABLE (OrderLineId int, OrderId int, ProductId int,                  Qty int, Price decimal(19,4), Discount decimal(5,4))ASBEGIN    INSERT @t    SELECT ol.OrderLineId, ol.OrderId, ol.ProductId, ol.Qty, ol.Price, ol.Discount    FROM dbo.Orders AS o    JOIN dbo.OrderLines AS ol ON ol.OrderId = o.OrderId    WHERE o.CustomerId = @CustomerId;    RETURN;END;GO -- Same text for all three customers: one plan, reused.EXEC sp_executesql    N'SELECT p.ProductId, COUNT(*) AS Zeilen, SUM(t.Qty * p.Listenpreis) AS Wert      FROM dbo.mstvf_KundenPositionen(@c) AS t      JOIN dbo.Products AS p ON p.ProductId = t.ProductId      GROUP BY p.ProductId      ORDER BY SUM(t.Qty * p.Listenpreis) DESC',    N'@c int',    @c = 99998;   -- first call, e.g. customer with 10 lines

The results:

• Interleaved execution applied on all four versions. On the first call, the estimate was exact.
• The first count stayed in place for all further calls. When the small customer came first, the plan for 10 rows (Nested Loops) stayed, even for 100,000. When the large one came first, the plan for 100,000 rows (Hash Match) stayed, even for 10. With interleaved execution turned off (DISABLE_INTERLEAVED_EXECUTION_TVF), the fixed estimate of 100 applied everywhere.
• The biggest difference did not come from this estimate. When the large customer came first, the small one then took 0.53–0.70 s instead of 1.4–1.9 ms. That held with and without interleaved execution. Our interpretation, not confirmed in the plan: the statements inside the function are also compiled with the first parameter and reused afterward, as in a stored procedure.
• An exact first estimate was not automatically faster. The large customer ran in 0.76–0.85 s with the plan for 10 rows, and in 0.95–1.14 s with the correctly estimated one. In return, the plan for 10 rows read five times as much (about 713,000 versus 138,000 reads), and on SQL Server 2017 and 2019 it moved the sort out to TempDB.

With a multi-statement function whose result set varies widely, the first call after compilation therefore decides the runtime of all later ones. An inline TVF removes this element of chance because the optimizer plans its query together with the calling one.

Which function for what?

How to proceed

1. Find functions. The inventory shows which scalar functions ran as a call of their own, and how often.
2. Get the actual plan. Check NonParallelPlanReason, whether the function appears as a call, and for table-valued functions the estimated versus actual rows.
3. Rewrite selectively. The table above shows the direction. After the rewrite, check that the result is identical, including NULL values and rounding.
4. Repeat after every upgrade. Compatibility level and cumulative updates change what happens to functions.

Where the measured times in Query Store come from when functions are involved, and why they can overlap, is shown in Where does the time go?.

Sources

• Microsoft: Scalar UDF Inlining
• Microsoft: Interleaved execution for multi-statement table-valued functions
• Microsoft Support: KB4538581 – Fixes for Scalar UDF Inlining
• Microsoft: sys.dm_exec_function_stats
• Microsoft: User-defined functions

More on abstraction: Nested views · Back to the overview: The Paradox of Abstraction

More on abstraction