FIX: Scalar UDF Inlining issues in SQL Server 2019

Dotyczy: SQL Server 2019 on LinuxSQL Server 2019 on Windows

Symptoms


User-Defined Functions (UDFs) that are implemented inTransact-SQL and that returned a single data value are referred to as T-SQL ScalarUser-Defined Functions (UDFs). 

Microsoft SQL Server 2019 introduces the Scalar UDFInlining feature that can improve the performance of queries that invoke T-SQLScalar UDFs, where UDF execution is the main bottleneck. T-SQL Scalar UDF Inlining automatically transforms inline-able UDFs into relational expressions. 

This cumulative update includes several fixes across thefollowing areas for scenarios in which a query that uses Scalar UDF Inlining mayreturn an error or unexpected results:
  • Type mismatch error if the return type of the UDF is 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 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)
This cumulativeupdate also blocks Inlining in the following scenarios:
  • If the UDF references certain intrinsic functions (e.g. @@ROWCOUNT) that may alter the results when inlined (added in Microsoft SQL Server 2019 CU2)
  • Aggregate functions being 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 (e.g. 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 inMicrosoft 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)
After you upgrade to Microsoft SQL Server 2019 CU2 but before CU5, to re-evaluate whether a UDF iseligible for inlining, refresh eligible scalar UDFs through either one of thefollowing methods:
The following scriptgenerates a script to refresh metadata for existing inlined scalar UDFs:
 
/*
      Generates a script that can be used to refresh all active inlineable ScalarUDFs.
      Note: sp_refreshsqlmodule does not affect any permissions, extended properties,
      or SET options that are associated with the object.
*/
SELECT 'EXECUTEsys.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 youupgrade to Microsoft SQL Server 2019 CU5, we will automatically re-derive UDFinlineability during compilation.  

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.