Scalar Functions and Performance

One neat function call can hide thousands of executions, which makes scalar functions worth checking in hot queries. A function called for every row of a large result can turn a modest query into a long-running one. SQL Server can inline eligible scalar functions in newer versions, but eligibility and actual plan behavior deserve inspection.

Why Row-by-Row Calls Add Up

A scalar user-defined function returns one value for each call. In older execution patterns, SQL Server invoked T-SQL scalar functions separately for rows reaching the expression. The function’s internal queries and calculations could be hard to see in the outer plan, and the call could limit parallel processing. Small work multiplied by many rows becomes meaningful.

The problem is not the word function. Built-in expressions and simple computed results are normal SQL. The concern is hidden work inside a user-defined routine called at high frequency. I check the row count at the function call site before deciding whether the routine is worth rewriting. How many rows reach that function call in your busiest execution?

Know What Inlining Changes for Scalar Functions

Scalar UDF inlining, introduced with SQL Server 2019 under appropriate compatibility settings, can substitute eligible function logic into the calling query during optimization. The optimizer then sees more of the work and can choose a better plan. It is a significant improvement, but it is not a promise for every function or every calling context.

Eligibility rules include function constructs, database settings, and query shape. A function can be marked inlineable yet not be inlined in a particular plan. I verify the actual plan and runtime behavior, rather than assuming the engine rescued every row-by-row routine after an upgrade.

Inspect Function Metadata

sys.sql_modules exposes is_inlineable and inline_type for SQL modules on supported versions. The first indicates whether the definition meets static criteria. The second indicates whether inlining is enabled for that module. These values guide review. The final decision also depends on the calling query and optimizer choice.

List functions in the target database and prioritize those appearing in costly, high-row-count queries. A tiny function called ten times is rarely the main problem. A function called once per fact row deserves attention even when its body looks harmless.

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
       o.name AS function_name,
       m.is_inlineable, m.inline_type
FROM sys.objects AS o
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.type = 'FN'
ORDER BY o.name;

Read the Actual Plan

Inspect the plan for a scalar function operator or inlined relational expressions, and compare execution time, CPU, and logical reads. When the function contains queries, account for their work as part of the end-to-end request. A plan estimate for the outer statement alone can understate the cost when function work is opaque.

I capture representative parameters and enough rows to reveal scaling. Testing one row can make any scalar function appear cheap. Compare ten, ten thousand, and the real production-sized result where feasible. If runtime rises sharply with output rows, investigate the function body and how many times it is evaluated.

Replace Simple Calculations Inline

A function that merely combines columns can be replaced by a direct expression, especially in a hot query. That exposes the expression to the optimizer and removes the separate routine call. Preserve NULL handling, precision, collation, and business rules when rewriting. A shorter query is not an improvement if it changes the answer.

This sample calculates net amount directly. It is a pattern, not a recommendation to inline every business rule everywhere. Centralized logic can be valuable when it is not a performance bottleneck.

SELECT OrderID,
       Subtotal - DiscountAmount + TaxAmount AS NetAmount
FROM dbo.Orders
WHERE OrderDate >= '2026-09-01'
  AND OrderDate < '2026-10-01';

Use an Inline Table-Valued Function

An inline table-valued function is a single SELECT expression that the optimizer can expand into the calling query. It is useful when you need reusable relational logic, such as a filtered set of orders, without hiding a procedural per-row operation. It still needs good predicates and indexes. An inline wrapper cannot make a costly join free.

Build the function around a set, then pass parameters that restrict the set. Inspect the caller’s plan to confirm the combined query is efficient. I prefer this route when the original scalar function queries tables and the business rule naturally returns rows or columns rather than a single isolated calculation.

CREATE OR ALTER FUNCTION dbo.ActiveOrdersForCustomer
(@CustomerID int)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderID, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
      AND Status = 'Active'
);
GO

SELECT OrderID, TotalAmount
FROM dbo.ActiveOrdersForCustomer(42);

Avoid a Blind Rewrite of Scalar Functions

An eligible scalar function can already inline well. Rewriting it without checking can increase maintenance work and introduce a semantic bug. Conversely, a function marked inlineable can be used in a context that prevents inlining. Check the actual call site, not just the definition. Version, compatibility level, and cumulative update behavior can affect results.

If a rewrite is needed, test correctness with NULLs, boundary values, and unusual rows. Compare total query cost under realistic concurrency. A microbenchmark of the function alone misses plan interactions with joins, sorting, and filtering. The goal is a better application query, not a prettier function inventory.

Watch for Table Access Inside Scalar Functions

A scalar function that looks like a formatting helper can query another table for every input row. That introduces repeated reads and can amplify blocking. Set-based joins or APPLY expressions can expose the relationship in one plan. Check whether the lookup has an index and whether missing rows or duplicates change the intended result.

I trace dependencies before changing such a function. A lookup routine can encode business rules that several reports share. Preserve the rule in a well-tested relational expression or view, then compare results. Removing a function call is straightforward. Preserving its meaning is the real engineering work.

Keep Performance Visible

Record the relevant query, row counts, plan shape, and runtime before making a change. After inlining or rewriting, check that CPU and reads improved and that the plan remains stable across important parameter values. A scalar function used in a small administrative query can be perfectly fine.

The dry joke is that one line of SQL can contain a surprising amount of work. Scalar functions are useful abstractions, but hot paths need transparency. Let the plan and measurements decide whether the abstraction earns its place or should become a set-based expression.

Related reading on this blog: SQL SERVER 2019: Disabling Scalar UDF Inlining and Interview Question of the Week #059: What are the Limitations of User Defined Functions (UDF) ?.

A scalar function is not automatically slow, it is risky when repeated work stays hidden.

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