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)
After you upgrade to Microsoft SQL Server 2019 CU2 or later, to re-evaluate whether a UDF iseligible for inlining, refresh eligible scalar UDFs through either one of thefollowing methods:
- Execute sp_refreshsqlmodule for applicable scalar UDFs (see sp_refreshsqlmodule (Transact-SQL) for details about this system stored procedure)
- Alter or re-create the existing scalar UDF with existing definition, permissions, and set properties (see ALTER FUNCTION (Transact-SQL))
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'
WHERE is_inlineable = 1
AND inline_type = 1;
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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: