Scalar UDF inlining

In one line: a scalar user-defined function used to run once per row and force the whole plan serial. Since SQL Server 2019, a function that qualifies is inlined: rewritten into the calling query as if its body had been typed there. Check whether yours qualifies, rather than assuming either way.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/scalar-udf-inlining on GitHub.

What you’ll see

  • A query with a scalar function in the SELECT list or WHERE clause that’s far slower than the same logic typed in. Without inlining, the demo ran 200× slower for arithmetic and 360× slower for a lookup.
  • A plan that stays serial, with NonParallelPlanReason="TSQLUserDefinedFunctionsNotParallelizable" (on 2019, CouldNotGenerateValidParallelPlan).
  • A lookup function doing one seek per row: half a million calls and a million reads.

Why it happens

Without inlining, the function is a black box called for every row, each call with its own context, and a plan containing one can’t go parallel. Inlined, it disappears from the plan. In the demo, the arithmetic function ran in 11 ms against 9 ms for the typed-in logic, in parallel, and the lookup function became an ordinary join with the same reads as the hand-written one.

What to do

  • Run at compatibility level 150+ on SQL Server 2019 or later, and check sys.sql_modules.is_inlineable for your functions.
  • Keep functions inlineable. Calling GETDATE() or another time-dependent function, or referencing a table variable, are among the documented restrictions. A function that calls GETDATE() showed is_inlineable = 0 and ran exactly like WITH INLINE = OFF.
  • Use WITH INLINE = OFF on the rare function whose inlined plan is worse. It also lets you compare the two without changing any database setting.

What doesn’t help

  • Hand-rewriting the function into the query without checking the answer. The function returned decimal(12,2), so every call rounded to two places before SUM. Inlining kept that. The hand-written “equivalent” computed at full precision and came out 50.00 different. Compare results, not just timings.
  • Assuming every function inlines on 2019+. Many don’t.

Check your server

-- Scalar functions in this database, and whether SQL Server can inline them.
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS function_name,
       m.is_inlineable,
       m.inline_type,
       DbCompatLevel = (SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID())
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE o.type = 'FN'
ORDER BY m.is_inlineable, function_name;

See it happen

The scalar-udf-inlining demo runs the same functions with inlining on and off: a calculation, a lookup and one that can’t be inlined. It measures time, parallelism, calls and reads, and compares the answers.

Related: parallelism · MSTVFs and table variables · data type traps

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers, compatibility level 160. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights