FIX: Scalar UDF Inlining issues in SQL Server 2019

Applies to: SQL Server 2019 on LinuxSQL Server 2019 on Windows

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). 
 
Microsoft SQL Server 2019 introduced the Scalar UDF Inlining feature that 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 includes several fixes across the following areas for scenarios in which a query that uses Scalar UDF Inlining may return an error or unexpected results:
 
  • Type mismatch error if the return type of the UDF is a sql_variant (added in Microsoft SQL Server 2019 CU2) 
  • UDF invocation from sp_executesql aborts execution (added in Microsoft SQL Server 2019 CU2)
  • UDFs referencing labels without an associated GOTO command return incorrect results (added in Microsoft SQL Server 2019 CU2)
  • Out-of-memory conditions and memory leaks occur because of very large scalar UDFs (added in Microsoft SQL Server 2019 CU2)
  • Uninitialized variables used in condition (IF-ELSE) statements cause errors (added in Microsoft SQL Server 2019 CU2)
  • Explicit conversion error if UDF has SQL_VARIANT parameter (added in Microsoft SQL Server 2019 CU7)
  • Error raised when scalar function references CHECKSUM (added in Microsoft SQL Server 2019 CU7)
  • UDF invocation with a high number of scalar expressions re-evaluations can result in a non-yielding scheduler error (added in Microsoft SQL Server 2019 CU7)
  • Error raised when UDF referencing query uses OPTION(RECOMPILE) (added in Microsoft SQL Server 2019 CU7)
This cumulative update also blocks inlining in the following scenarios:
 
  • If the UDF references certain intrinsic functions (for example, @@ROWCOUNT) that may alter the results when inlined (added in Microsoft SQL Server 2019 CU2)
  • When aggregate functions are passed as parameters to a scalar UDF (added in Microsoft SQL Server 2019 CU2)
  • If the UDF references built-in views (for example: OBJECT_ID) (added in Microsoft SQL Server 2019 CU2)
  • If the UDF uses XML methods (added in Microsoft SQL Server 2019 CU4)
  • If the UDF contains a SELECT with ORDER BY without a "TOP 1" (added in Microsoft SQL Server 2019 CU4)
  • If the SELECT query performs an assignment in conjunction with the ORDER BY clause (for example, SELECT @x = @x +1 FROM table ORDER BY column_name) (added in Microsoft SQL Server 2019 CU4)
  • If the UDF contains multiple RETURN statements (added in Microsoft SQL Server 2019 CU5)
  • If the UDF is called from a  RETURN statement (added in Microsoft SQL Server 2019 CU5)
  • If the UDF references the STRING_AGG function (added in Microsoft SQL Server 2019 CU5)
  • If the UDF definition references remote tables (added in Microsoft SQL Server 2019 CU6)
  • If the UDF-calling query uses GROUPING SETS, CUBE, or ROLLUP (added in Microsoft SQL Server 2019 CU6)
  • If the UDF-calling query contains a variable that is used as a UDF parameter for assignment (for example, SELECT @y=2, @x=UDF(@y)) (added in Microsoft SQL Server 2019 CU6)
After you upgrade to Microsoft SQL Server 2019 CU2 but before you upgrade to 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:
 
The following script generates a script to refresh metadata for 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 Microsoft SQL Server 2019 CU5, we will automatically derive UDF inlineability during compilation again.

Status


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

Resolution


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

About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

References


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