Nonfoldable User Defined Functions

Most SQL Server professionals know that wrapping a column in a function can kill SARGability. Far fewer realize that putting a user-defined function on the other side of the predicate can be just as dangerous. The reason isn’t the function itself. It’s what happens when the optimizer is unable to perform constant folding. Let’s explore how a single nonfoldable function can completely change the way SQL Server estimates, optimizes, and executes a query.

Folding vs. Nonfolding Expressions

One of the lesser-known optimizations performed by the SQL Server Query Optimizer is constant folding. During query compilation, SQL Server evaluates deterministic expressions whose result can be calculated in advance and replaces the expression with a constant value. Instead of repeatedly evaluating DATEADD(DAY, -30, '2025-01-31'), the optimizer can simply substitute the resulting date before execution even begins.

This seemingly small optimization provides two important benefits. First, the expression no longer needs to be evaluated during query execution. Second, and far more importantly, the optimizer can use the resulting constant value to make better decisions. Knowing the exact boundary value allows SQL Server to leverage column statistics, estimate cardinalities more accurately, and often select more efficient access methods such as index seeks.

A nonfoldable expression is the opposite. If SQL Server cannot safely evaluate an expression during compilation, the expression remains in the query tree and must be processed at execution time. This commonly occurs with nondeterministic functions, local variables, and certain user-defined functions. As a consequence, the optimizer may be unable to determine the actual value used in a predicate, leading to less accurate estimates and potentially suboptimal execution plans.

A Tale of Two Predicates

At first glance, the following two predicates appear to be identical. Both compare the same column, both use the same date boundary, and both ultimately evaluate to exactly the same value. Most SQL Server professionals would expect the optimizer to generate identical execution plans. After all, what difference could there be between a literal expression and a function that merely returns that same expression? As it turns out, the difference is far more significant than most people realize. Before we look at the execution plans, let’s start with a simple user-defined function that subtracts 30 days from a given date:

CREATE OR ALTER FUNCTION dbo.get_diff_date
(
	@date	DATE
)
RETURNS DATE
AS
BEGIN
	DECLARE	@return_value DATE = DATEADD(DAY, -30, @date);
	RETURN	@return_value;
END
GO

Now let’s compare two predicates. The first uses the expression directly, while the second delegates exactly the same calculation to the user-defined function. Although both predicates are logically equivalent and return the same result, SQL Server processes them very differently.

/* performant query because of constant folding */
SELECT	*
FROM		dbo.orders
WHERE	o_orderDate < DATEADD(day, -30, '2013-01-31')
ORDER BY
		o_orderkey DESC
OPTION 
(
    RECOMPILE, 
    QUERYTRACEON 3604,
    QUERYTRACEON 8605,
	QUERYTRACEON 9130
);
GO

/* Non performant query because of Nonfoldable UDF */
SELECT	*
FROM		dbo.orders
WHERE o_orderDate < dbo.get_diff_date('2013-01-31')
ORDER BY
		o_orderkey DESC
OPTION 
(
    RECOMPILE, 
    QUERYTRACEON 3604,
    QUERYTRACEON 8605,
	QUERYTRACEON 9130
);
GO

For the purpose of demonstrating folding versus nonfolding expressions, the following options help expose what the optimizer is doing internally

Why One Predicate Gets Folded and the Other Doesn’t?

At this point, the two queries appear to be functionally identical. Both produce the same result, both compare the same column against the same date value, and both seem to express the same business logic. Yet SQL Server treats them very differently during compilation. To understand why, we need to look beyond the T-SQL syntax and examine what the optimizer actually sees internally. The answer becomes obvious once we inspect the converted query tree, where one predicate is reduced to a constant while the other survives as a function call.

Foldable Expression

The execution plan already suggests an efficient optimization strategy, but the converted tree reveals why SQL Server was able to generate it.

*** Converted Tree: ***
    LogOp_OrderBy QCOL: [ERP_Demo].[dbo].[orders].o_orderkey DESC 
        LogOp_Project QCOL: [ERP_Demo].[dbo].[orders].o_orderdate ...
            LogOp_Select
                LogOp_Get TBL: dbo.orders dbo.orders TableID=2022298264 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                ScaOp_Comp x_cmpLt
                    ScaOp_Identifier QCOL: [ERP_Demo].[dbo].[orders].o_orderdate
                    ScaOp_Const TI(datetime,Null,ML=8) XVAR(datetime,Not Owned,Value=(days,300secs)=(41273,0))
            AncOp_PrjList

The real significance of this plan is not what it contains, but what it does not contain. There is no Compute Scalar calculating the date boundary for every row, and there is no indication that the expression participates in row-by-row processing. The predicate has already been reduced to a simple search condition before execution begins!

ScaOp_Const tells us that SQL Server has already evaluated the original expression during compilation and replaced it with a constant value. The presence of ScaOp_Const is therefore direct evidence that constant folding occurred and that the original expression has been reduced to a literal value before optimization begins.

Nonfoldable Expression

While the execution plan shows the symptom, the converted tree exposes the root cause.

*** Converted Tree: ***
    LogOp_OrderBy QCOL: [ERP_Demo].[dbo].[orders].o_orderkey DESC 
        LogOp_Project QCOL: [ERP_Demo].[dbo].[orders].o_orderdate ...
            LogOp_Select
                LogOp_Get TBL: dbo.orders dbo.orders TableID=2022298264 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                ScaOp_Comp x_cmpLt
                    ScaOp_Identifier QCOL: [ERP_Demo].[dbo].[orders].o_orderdate
                    ScaOp_Udf dbo.get_diff_date IsNonDet 
                        ScaOp_Const TI(date,Null,ML=3) XVAR(date,Not Owned,Value=(734898))

While the function itself remains intact, SQL Server successfully converts the function’s input parameter into an internal constant representation. This means the optimizer knows the exact value being supplied to the function. However, knowing the input value is not enough to perform constant folding. The optimizer would also need to evaluate the function and replace the complete function call with its resulting value. Since that does not happen, the constant merely serves as the argument for the function invocation represented by the parent ScaOp_Udf node. As a result, the query tree retains the user-defined function instead of replacing it with a single ScaOp_Const node!

Making Reusable Code Foldable Again

The previous example demonstrated that encapsulating a simple expression inside a scalar UDF can prevent SQL Server from performing constant folding. Fortunately, this does not mean that you have to abandon reusable code altogether. Modern versions of SQL Server offer two approaches that preserve maintainability while giving the optimizer greater visibility into the underlying logic.

• The first option is to replace the scalar UDF with an inline table-valued function (iTVF). Because the function definition is expanded directly into the calling query, the optimizer can work with the actual expression and apply optimizations such as constant folding.
• The second option is available in SQL Server 2019 and later, where Scalar UDF Inlining was introduced as part of the Intelligent Query Processing feature set. Under the right conditions, SQL Server can automatically transform eligible scalar UDFs into equivalent relational expressions during optimization, effectively removing the function boundary that previously prevented advanced optimizations.

Inline Table-Valued Function

An effective way to preserve code reuse while enabling constant folding is to replace the scalar UDF with an inline table-valued function (iTVF). Unlike a scalar UDF, an iTVF does not form an optimization barrier. During query optimization, SQL Server expands the function definition directly into the calling query, making the underlying expression visible to the optimizer.

From a constant-folding perspective, this visibility is the crucial difference. When the optimizer encounters a scalar UDF, it only sees a function call and cannot replace the entire expression with a constant value. In contrast, after an inline table-valued function is expanded, the optimizer sees the actual expression that produces the result.

/* Make the SCALAR function an INLINE function */
CREATE OR ALTER FUNCTION dbo.get_diff_date
(
	@date	DATE
)
RETURNS TABLE
AS
RETURN
	SELECT DATEADD(DAY, -30, @date) AS date_diff;
GO

Let SQL Server Do the Heavy Lifting

Starting with SQL Server 2019, Microsoft introduced Scalar UDF Inlining, a feature that allows the optimizer to automatically transform eligible scalar user-defined functions into equivalent relational expressions during query optimization. Instead of treating the function as a black box, the optimizer can effectively remove the function boundary and work directly with the underlying logic.

From a constant-folding perspective, this can be a game changer. Once the function body becomes visible to the optimizer, expressions that were previously hidden behind a scalar UDF can become eligible for the same optimization rules that apply to ordinary T-SQL expressions. In many cases, SQL Server can evaluate the expression during compilation and replace it with a constant value before execution begins.

The key advantage of this approach is that it requires no architectural changes to the application. Existing scalar UDFs can often benefit from the feature automatically, provided they satisfy the inlining requirements. This allows developers to retain their familiar programming model while giving the optimizer an opportunity to perform transformations such as constant folding, improve cardinality estimates, and generate more efficient execution plans.

The lesson is simple: reusable code is valuable, but every abstraction hides information from the optimizer. When that abstraction prevents constant folding, a seemingly harmless function call can have a dramatic impact on query performance. Fortunately, modern SQL Server versions provide multiple ways to keep your code reusable without sacrificing optimization opportunities.

Thank you for reading!