User-Defined Functions (UDFs) that are implemented inTransact-SQL and that return 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 inlineable 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 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)
This cumulativeupdate 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 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)
- If the UDF definition references remote tables (added inMicrosoft 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 aUDF parameter for assignment (for example, SELECT @y=2, @x=UDF(@y)) (added inMicrosoft 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 iseligible for inlining. To do this, refresh eligible scalar UDFs through either 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 by using 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;
Note: After youupgrade to Microsoft SQL Server 2019 CU5, we will automatically derive UDFinlineability during compilation again.
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: