As you already know from Chapter 8, a scalar user-defined function (UDF) is a function that returns an atomic (scalar) value. These functions have a significant performance drawback, because the query optimizer does not handle them optimally. The reason is that their execution is separated from the rest of the execution plans, meaning they get called once for every row and cannot be optimized based on the estimated number of rows.
SQL Server 2019 introduces a new feature, called Scalar UDF Inlining, which incorporates each scalar UDF into the overall plan. That way, the performance of queries that invoke scalar UDFs can be significantly improved.
In the following examples, we will create a function and execute it twice, first without using the new feature, and then with the Scalar UDF Inlining feature. Example 28.11 creates the function.
Example 28.11
ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 140;
GO
USE AdventureWorks;
GO
CREATE FUNCTION dbo.CustomerRate14 (@CustomerID INT)
RETURNS CHAR(10) AS
BEGIN;
DECLARE @sales DECIMAL (18,2);
DECLARE @category CHAR(10);
SET @sales = (SELECT SUM(Subtotal)
FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID);
IF @sales < 500000
SET @category = ‘REGULAR’;
ELSE IF @sales < 1000000
SET @category = ‘GOLD’;
ELSE
SET @category = ‘PLATINUM’;
RETURN @category;
END
The first statement in Example 28.11 sets the compatibility level of the AdventureWorks database to 140, which is the compatibility level of SQL Server 2017. That way, the Scalar UDF Inlining feature is indirectly disabled, because SQL Server 2017 does not support it.
After that, the CustomerRate14 function is created. This function determines the sales category for a customer, which is identified using a unique customer key. The function first computes the total sales of all orders placed by that customer. Then, the function uses IF-ELSE logic to decide the category based on the customer’s sales.
Example 28.12 defines a query that invokes the CustomerRate14 function and measures the elapsed time of the query.
Example 28.12
USE AdventureWorks;
set STATISTICS TIME ON;
SELECT c.CustomerID, dbo.CustomerRate14(c.CustomerID)
FROM Sales.Customer AS c;
set STATISTICS TIME OFF;
The elapsed time of this query on my computer is 1544 ms.
For every scalar UDF, you can use the sys.sql_modules catalog view to check whether or not a user-defined function can be inlined. This view includes a property called is_inlineable. A value of 1 indicates that the particular function can be inlined, and 0 indicates otherwise. Example 28.13 shows the use of the sys.sql_modules catalog view in relation to the function created in Example 28.12. (The result of this query is 1.)
Example 28.13
— checking, whether a UDF can be inlined
SELECT is_inlineable
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id=o.object_id
AND o.name = ‘CustomerRate14’
NOTE If a scalar UDF is inlineable, it does not imply that it will always be inlined. The Database Engine will decide (on a per-query, per-UDF basis) whether to inline the particular UDF or not. For instance, if the UDF definition runs into thousands of lines of code, the Database Engine might choose not to inline it. This decision is made when the query referencing a scalar UDF is compiled.
Example 28.14 repeats the creation of the same function and the identical query, but with the compatibility level set to 150 for SQL Server 2019.
Example 28.14
ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 150;
GO
USE AdventureWorks;
GO
CREATE FUNCTION dbo.CustomerRate15 (@CustomerID INT)
RETURNS CHAR(10) AS
BEGIN;
DECLARE @sales DECIMAL (18,2);
DECLARE @category CHAR(10);
SET @sales = (SELECT SUM(Subtotal)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID);
IF @sales < 500000
SET @category = ‘REGULAR’;
ELSE IF @sales < 1000000
SET @category = ‘GOLD’;
ELSE
SET @category = ‘PLATINUM’;
RETURN @category;
END
GO
SET STATISTICS TIME ON;
SELECT c.CustomerID, dbo.CustomerRate15(c.CustomerID)
FROM Sales.Customer AS c;
set STATISTICS TIME OFF;
The elapsed time of the query for Example 28.14 on my computer is 980 ms.
Enabling and Disabling Scalar UDF Inlining
This feature is enabled by default for SQL Server 2019, which means you don’t have to make any changes to a particular UDF or a query using that UDF to take advantage of the feature.
To disable Scalar UDF Inlining at the database level, use the ALTER DATABASE statement for the current database, as follows:
ALTER DATABASE SCOPED CONFIGURATION
SET TSQL_SCALAR_UDF_INLINING = OFF;
You can also disable Scalar UDF Inlining for a specific query by designating DISABLE_ TSQL_SCALAR_UDF_INLINING as a USE HINT query hint. The USE HINT query hint takes precedence over the database-scoped configuration or compatibility level setting.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.