KB4538581 - FIX: Scalar UDF Inlining issues in SQL Server 2022 and 2019

Symptoms

User-Defined Functions (UDFs) that are implemented in Transact-SQL and that return a single data value are known as T-SQL Scalar User-Defined Functions (UDFs). 

The Scalar UDF Inlining feature was introduced in Microsoft SQL Server 2019. This feature can improve the performance of queries that invoke T-SQL Scalar UDFs in which UDF execution is the main bottleneck. T-SQL Scalar UDF Inlining automatically transforms inlineable UDFs into relational expressions. 

This cumulative update (CU) includes several fixes across the following areas for scenarios in which a query that uses Scalar UDF Inlining might return an error message or unexpected results:

• Type mismatch error occurs if the return type of the UDF is a sql_variant (added in SQL Server 2019 CU2).

• UDF invocation from sp_executesql cancels execution (added in SQL Server 2019 CU2).

• UDFs that reference labels without an associated GOTO command return incorrect results (added in SQL Server 2019 CU2).

• Out-of-memory conditions and memory leaks occur because of very large scalar UDFs (added in SQL Server 2019 CU2).

• Uninitialized variables that are used in condition (IF-ELSE) statements cause errors (added in SQL Server 2019 CU2).

• Explicit conversion error occurs if a UDF has the sql_variant parameter (added in SQL Server 2019 CU7).

• Error occurs if the scalar function references CHECKSUM (added in SQL Server 2019 CU7).

• UDF invocation that has many scalar expression re-evaluations can cause a non-yielding scheduler error (added in SQL Server 2019 CU7).

  Note: The fix for this issue might sometimes cause a performance regression. To mitigate this performance regression, you can disable the original fix by turning on trace flag (TF) 13156.

  Microsoft is working on a fix for this performance regression that will be available in a future CU.

• Error occurs if the UDF referencing query uses OPTION (RECOMPILE) (added in SQL Server 2019 CU7).

• Permission errors occur if views invoke inlined UDFs (added in SQL Server 2019 CU9).

• Access violations occur if the query_tsql_scalar_udf_inlined XEvent is used (added in SQL Server 2019 CU9).

• Inconsistent behavior occurs because Scalar UDF Inlining does not respect the QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n query hint (added in SQL Server 2019 CU9).

• Inlining UDFs contain aggregations that can cause non-yielding scheduler errors (added in SQL Server 2019 CU9).

• Out-of-memory errors occur because of a sequence of scalar operations on the same variable in conditional blocks (added in SQL Server 2019 CU9).

• After you upgrade to CU9, an access violation occurs if an object invokes a scalar inlineable UDF (UDF1) that has a scalar inlineable UDF (UDF2) that's used as an input parameter (added in SQL Server 2019 CU11).

• Inlining UDFs might cause errors and generate error codes 6846, 1011, and 107 (added in SQL Server 2019 CU11).

• Inlining UDFs that run as the non-sysadmin account might return the error message, "A severe error occurred on the current command.  The results, if any, should be discarded" (added in SQL Server 2019 CU16).

• An access violation occurs if temporary tables are invoked inside UDFs by using synonyms (added in Microsoft SQL Server 2022 CU1 and SQL Server 2019 CU19).

• An access violation occurs if the UDF definition contains only GOTO labels and a RETURN statement (added in SQL Server 2022 CU1 and SQL Server 2019 CU19).

• Scalar UDF returns results in different date and time formats when the Scalar UDF Inlining feature is turned on (added in SQL Server 2022 CU3 and SQL Server 2019 CU20).

• A dump file is generated if a query that has a GROUP BY clause uses inlined UDFs in a SELECT statement without an aggregate function (added in SQL Server 2022 CU12 and SQL Server 2019 CU26).

• Error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" occurs if the Scalar UDF Inlining feature is turned on, even if the nesting level isn't exceeded (added in SQL Server 2022 CU12 and SQL Server 2019 CU26).

• Memory corruption occurs in sqllang.dll!CProchdr::FDeriveInlineabilityOfScalarUdf if the Scalar UDF Inlining feature is turned on (added in SQL Server 2022 CU12 and SQL Server 2019 CU26).

This cumulative update also blocks inlining in the following scenarios:

• If the UDF references certain intrinsic functions (for example, @@ROWCOUNT) that might alter the results when the UDF is inlined (added in SQL Server 2019 CU2).

• When aggregate functions are passed as parameters to a scalar UDF (added in SQL Server 2019 CU2).

• If the UDF references built-in views (for example: OBJECT_ID) (added in SQL Server 2019 CU2).

• If the UDF uses XML methods (added in SQL Server 2019 CU4).

• If the UDF contains a SELECT statement that uses the ORDER BY clause but not TOP 1 (added in SQL Server 2019 CU4).

• If the SELECT statement performs an assignment together with the ORDER BY clause (for example, SELECT @x = @x +1 FROM table ORDER BY column_name) (added in SQL Server 2019 CU4).

• If the UDF contains multiple RETURN statements (added in SQL Server 2019 CU5).

• If the UDF is called from a RETURN statement (added in SQL Server 2019 CU5).

• If the UDF references the STRING_AGG function (added in SQL Server 2019 CU5).

• If the UDF definition references remote tables (added in SQL Server 2019 CU6).

• If the UDF-calling query uses GROUPING SETS, CUBE, or ROLLUP (added in SQL Server 2019 CU6).

• If the UDF-calling query contains a variable that's used as a UDF parameter for assignment (for example, SELECT @y=2, @x=UDF(@y)) (added in SQL Server 2019 CU6).

• If the UDF references encrypted columns (added in SQL Server 2019 CU11).

• If the UDF contains references to WITH XMLNAMESPACES (added in SQL Server 2019 CU11).

• If the query invoking the UDF has Common Table Expressions (CTEs) (added in SQL Server 2019 CU11).

• If the UDF definition contains temporary tables or synonyms for temporary tables (added in SQL Server 2022 CU1 and SQL Server 2019 CU19).

• If the UDF definition has a SELECT statement together with a DISTINCT clause on multiple assignment columns (added in SQL Server 2022 CU4 and SQL Server 2019 CU20).

• If the UDF contains a SELECT statement that uses the TABLESAMPLE PERCENT clause and multiple variable assignments (added in SQL Server 2022 CU7).

• If an INSERT query exists on a table that's referenced by an indexed view that contains a UDF (added in SQL Server 2022 CU12 and SQL Server 2019 CU26).

After you upgrade to SQL Server 2019 CU2, but before you upgrade to SQL Server 2019 CU5, you should re-evaluate whether a UDF is eligible for inlining. To do this, refresh eligible scalar UDFs through either of the following methods:

• Run sp_refreshsqlmodule for applicable scalar UDFs. For more information about this system stored procedure, see sp_refreshsqlmodule (Transact-SQL).

• Alter or re-create the existing scalar UDF by using existing definitions, permissions, and set properties. For more information, see ALTER FUNCTION (Transact-SQL).

The following command generates a script to refresh the metadata of any existing inlined scalar UDFs:

/*

Generates a script that can be used to refresh all active inlineable scalar UDFs.

Note The sp_refreshsqlmodule command does not affect any permissions, extended properties, or SET options that are associated with the object.

*/

SELECT 'EXECUTE sys.sp_refreshsqlmodule ''[' + OBJECT_SCHEMA_NAME(object_id) + +'].' + '[' + OBJECT_NAME(object_id)

+ ']'';' AS 'RefreshStatement'

FROM sys.sql_modules

WHERE is_inlineable = 1

AND inline_type = 1;

GO

Note: After you upgrade to SQL Server 2019 CU5, we will resume automatically deriving UDF inlineability during compilation.

Resolution

This issue is fixed in the following cumulative updates for SQL Server:

• Cumulative Update 12 for SQL Server 2022

• Cumulative Update 26 for SQL Server 2019

Status

Microsoft has confirmed that this is an issue in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminology that Microsoft uses to describe software updates.