Scalar UDF Inlining: When Old Functions Suddenly Get Fast

A small scalar function can hide substantial work when a query calls it for many rows. Scalar UDF inlining lets SQL Server incorporate eligible function logic into the calling query instead of keeping that work behind a separate invocation.

What Scalar UDF Inlining Changes in the Plan

Traditional scalar T-SQL functions can execute iteratively as the calling query processes rows. Their internal work is difficult for the outer optimizer to cost and optimize as one relational expression. A short function body does not guarantee a cheap workload when the same operation repeats across a large input.

SQL Server 2019 introduced a transformation for eligible functions and eligible calling contexts. The function's expressions and queries become part of the calling plan. That gives the optimizer a broader view of the work and can remove restrictions associated with the separate function invocation. It does not make every function eligible or guarantee a faster plan.

I inspect the calling query rather than treating the function's source length as a performance measurement. The useful question is how its logic executes for the representative input. A function with three lines can be an expensive abstraction; a longer function can be irrelevant to the workload under investigation.

Verify the Version and Database Settings

The feature requires SQL Server 2019 or later and database compatibility level 150 or higher. Check the actual database context before interpreting metadata or plans. A newer engine can host a database at an older compatibility level, so its installed version alone does not establish whether the transformation is available.

SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'TSQL_SCALAR_UDF_INLINING';

Use the following configuration change only in an approved rehearsal database. Record the previous value and compare the affected workload before changing a production setting. Raising compatibility level has a wider effect than enabling one feature, so do not treat it as a narrow substitute for investigating this function.

ALTER DATABASE SCOPED CONFIGURATION
SET TSQL_SCALAR_UDF_INLINING = ON;

Current cumulative updates matter because eligibility rules and correctness fixes have evolved. Keep the supported engine build in the test record. A plan captured on one older build is not a permanent promise about every later compilation on a different build or context.

Create a Simple Eligible Candidate

This example computes a discounted price with one return expression. Create it in a scratch database in its own batch. The decimal parameter and return types make the numeric contract visible. Keep the same contract when comparing transformed and untransformed execution so rounding changes do not masquerade as a performance improvement.

CREATE OR ALTER FUNCTION dbo.DiscountedPrice
(
    @Price decimal(12,2),
    @Discount decimal(5,4)
)
RETURNS decimal(12,2)
WITH INLINE = ON
AS
BEGIN
    RETURN @Price * (1 - @Discount);
END;
GO

INLINE ON asks for an eligible definition and produces an error when the definition does not meet the required conditions. It does not force every calling query to use the transformation. The optimizer still evaluates the compilation context. Leaving the option unspecified lets SQL Server derive the function's setting from its eligibility.

Inspect Eligibility Without Overclaiming

The module catalog distinguishes the definition's eligibility from whether transformation is enabled for it. Filter to scalar SQL functions so the report does not mix in unrelated module types. Metadata visibility still applies; use an identity permitted to inspect the objects in the review scope.

SELECT s.name AS SchemaName, o.name AS FunctionName,
       m.is_inlineable, m.inline_type, m.definition
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.type = 'FN' AND o.is_ms_shipped = 0;

A value of one for is_inlineable is a candidate signal, not evidence that the current calling query actually expanded the function. The query's use, optimizer choices, and other restrictions still matter. Inspect the actual plan to resolve that question instead of labeling every eligible function already optimized.

Common Blockers for Scalar UDF Inlining

Time-dependent expressions such as GETDATE, table variables, and table-valued parameters prevent this transformation in affected scalar functions. Other restrictions involve execution context, remote access, return structure, and particular language constructs. Review the documented rules for the supported build rather than trying to memorize an old universal checklist.

If a function reads the current time, consider whether the caller should supply a deliberately chosen timestamp instead. That design also makes business-time semantics explicit. Passing a timestamp changes the interface, so verify all callers and test the intended behavior before using it as a tuning shortcut.

Do not mechanically rewrite a function solely to make the eligibility bit change. A rewrite that alters NULL handling, decimal precision, transaction-visible reads, or boundary logic can create a correctness problem despite an attractive plan. Keep expected outputs for edge cases alongside the plan comparison. An eligibility flag has excellent manners and no knowledge of your business rule.

Compare the Calling Query in Both Modes

Create synthetic data with known input values, then compare equivalent executions with actual plans enabled. The following population uses a fixed ten-digit cross product to construct inputs without requiring a newer data-generation function. Its row population is part of the sample setup, rather than an observed workload statistic.

CREATE TABLE #SaleLines
(
    SaleID int NOT NULL PRIMARY KEY,
    CategoryID int NOT NULL,
    Price decimal(12,2) NOT NULL,
    Discount decimal(5,4) NOT NULL
);
WITH Digits AS
(
    SELECT value FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(value)
), Numbers AS
(
    SELECT 1 + a.value + 10*b.value + 100*c.value + 1000*d.value AS n
    FROM Digits AS a CROSS JOIN Digits AS b
    CROSS JOIN Digits AS c CROSS JOIN Digits AS d
)
INSERT #SaleLines
SELECT n, n % 10, CAST(10 + n % 500 AS decimal(12,2)), 0.1000
FROM Numbers;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT CategoryID, SUM(dbo.DiscountedPrice(Price,Discount)) AS TotalAmount
FROM #SaleLines GROUP BY CategoryID
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));
SELECT CategoryID, SUM(dbo.DiscountedPrice(Price,Discount)) AS TotalAmount
FROM #SaleLines GROUP BY CategoryID;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Review function-related plan XML and the expanded expressions. Successful expansion removes the separate UserDefinedFunction node for that invocation. Compare actual CPU, elapsed time, reads, and result equivalence under a controlled repetition policy. Do not assign a promised speedup to these statements before executing them on the target environment.

Control Scalar UDF Inlining at the Right Scope

The query hint disables scalar UDF inlining for one statement without changing the whole database. The database setting controls a broader population. A function-level INLINE OFF option is appropriate when that function specifically needs to remain outside the transformation while other eligible functions keep their existing behavior.

CREATE OR ALTER FUNCTION dbo.DiscountedPrice
(
    @Price decimal(12,2),
    @Discount decimal(5,4)
)
RETURNS decimal(12,2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @Price * (1 - @Discount);
END;
GO

This final example changes the lab function to demonstrate the control. Restore the intended definition after the experiment. Which scope addresses the observed issue with the fewest unrelated changes? Choose it from execution evidence rather than disabling the feature across an entire estate because one query needs investigation.

Verify Results Before Keeping the Change

I retain both execution evidence and correctness checks before accepting a tuning result. Include representative data skew, NULLs, boundary discounts, and the actual application query shapes. A demonstration aggregate is useful for understanding the transformation but does not stand in for every production caller.

Scalar UDF inlining can expose previously hidden work to optimization. Keep that explanation separate from a measured improvement, and document cases where the plan remains unexpanded. The practical outcome is a verified calling plan and a supported numeric contract, not simply an enabled setting and a hopeful expectation.

Related reading on this blog: Scalar Functions and Performance and Intelligent Query Processing: Checking What Is On per Database.

Function eligibility is not a measured speedup, it is permission for the optimizer to consider a different execution strategy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.