SQL Server › User Defined Functions

What improvements did SQL Server 2019 introduce for scalar function performance through a feature called scalar UDF inlining?

Intermediate

Answer

Scalar UDF inlining allows the optimizer to automatically transform certain qualifying scalar functions into an equivalent inline expression as part of building the execution plan, effectively eliminating much of the traditional row by row execution overhead, though this optimization only applies to functions that meet specific criteria, such as not containing certain unsupported constructs.

-- A function eligible for scalar UDF inlining
CREATE FUNCTION dbo.CalculateDiscount (@Amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS BEGIN RETURN @Amount * 0.9; END;

-- Check if inlining is being applied
SELECT * FROM sys.sql_modules WHERE is_inlineable = 1;

Real-world example A team upgrading to a recent SQL Server version notices significant performance improvements in reports using scalar functions, discovering many of their functions now automatically qualify for scalar UDF inlining without any code changes.

Common follow-ups: What specific function constructs disqualify a scalar function from being inlined?;How do you check whether a specific function is actually being inlined in practice?

Query Optimization & Plans;SQL Server Architecture & Editions