A Scalar UDF works consistently in SQL Server 2017, fails sporadically under 2019 and 2022.

Question

A Scalar UDF works consistently in SQL Server 2017, fails sporadically under 2019 and 2022.

Steve Grimes 20

You can try this at home!

I use this udf to capture the number of cores available when my stored procedure runs in cloud. But, since I develop against my local on-prem database, I coded it to return a -1 when it's not running in the cloud. Here's the whole thing:

CREATE OR ALTER FUNCTION dbo.sfnGetCores()
RETURNS SMALLINT
AS
BEGIN

DECLARE @Result SMALLINT = NULL

IF OBJECT_ID('sys.dm_db_resource_stats') IS NOT NULL
    SELECT @Result = cpu_limit FROM sys.dm_db_resource_stats WITH (NOLOCK) ORDER BY end_time DESC

IF @Result IS NULL SET @Result = -1

RETURN @Result
END
GO

Now, when I run this

SELECT dbo.sfnGetCores() AS Cores

in SSMS against a SQL Server 2017 compatibility level database, it always works. When I change the compatibility level to 2019 or 2022, it works sometimes and fails sometimes. If it works, I add few spaces, or few empty lines, then run again. It will fail once - then start working again. I get a similar "variety" of working/failing when I embed this in a stored procedure.

I suspect the introduction of scalar udf inlining in 2019 may be related, but this line from the documented requirements says it should be excluded as an inline candidate.
"The UDF doesn't reference built-in views (such as OBJECT_ID) 4."

I suspect when it uses a cached plan, it doesn't fail. This:

SELECT dbo.sfnGetCores() AS Cores OPTION (RECOMPILE)

works consistently in 2017 and fails consistently in 2019 and 2022.

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Akhil Gajavelly 1,570 Microsoft External Staff Moderator

Hi @Steve Grimes ,

This is due to Scalar UDF Inlining in SQL Server 2019+.

When the function gets inlined, sys.dm_db_resource_stats is resolved at compile time before your OBJECT_ID() check runs. That’s why it behaves inconsistently in 2019/2022 but works in 2017.

Just add: WITH INLINE = OFF

After adding WITH INLINE = OFF, it will work consistently.

Thanks,
Akhil.

Steve Grimes 20 Reputation points

2026-02-24T14:55:33.2633333+00:00

Thank you! Tested and confirmed!

Answer 2

The issue you're experiencing with the scalar UDF dbo.sfnGetCores() in SQL Server 2019 and 2022 may indeed be related to the scalar UDF inlining feature introduced in SQL Server 2019. While the requirements for inlining state that the UDF should not reference built-in views like OBJECT_ID, the behavior you're observing could be influenced by how SQL Server optimizes and caches execution plans for UDFs.

In SQL Server 2017, the UDF is executed as expected without the inlining optimizations, which may explain why it works consistently there. However, in SQL Server 2019 and 2022, the introduction of inlining can lead to inconsistencies, especially if the function's execution plan is cached and reused in a way that doesn't account for the current context (like the availability of sys.dm_db_resource_stats).

When you use OPTION (RECOMPILE), it forces SQL Server to create a new execution plan each time, which can lead to different behavior if the underlying conditions change between executions. This might explain why it fails consistently in 2019 and 2022 when you use that option, as the plan may not be optimal for the current execution context.

To troubleshoot further, you might consider:

Reviewing the execution plan to see how SQL Server is treating the UDF in different compatibility levels.
Testing the UDF in various contexts to determine if certain conditions lead to failures.
Checking for any updates or patches for SQL Server 2019 and 2022 that might address known issues with scalar UDFs.

Overall, the behavior you're seeing is likely tied to the changes in how SQL Server handles UDFs starting from 2019, particularly with inlining and execution plan caching.

Share via

A Scalar UDF works consistently in SQL Server 2017, fails sporadically under 2019 and 2022.

1 additional answer

Your answer