Keeping DRY in SQL Server with Scalar User Defined Functions – Part 2

In my previous post, Keeping DRY in SQL Server with Scalar User Defined Functions – Part 1, I talked about how good intentions of following good programming practices can lead to unintended consequences in SQL Server. We looked at the effect that using scalar User Defined Functions (scalar UDFs) to encapsulate common query logic can have on the performance of your system. In this post, we’ll carry that topic on into the world of SQL Server 2019, which introduces some enhancements in this very area.

Recap

First, a quick recap on the scenario we ran through previously, using the WorldWideImporters OLTP database.

We created this function that, given an order ID, will calculate the total of the items on that order.

CREATE FUNCTION Sales.CalculateOrderLineTotal ( 
@OrderId INTEGER 
) 
RETURNS DECIMAL(18,2) 
AS 
BEGIN 
DECLARE @Total DECIMAL(18,2) 
 
SELECT @Total = SUM(Quantity * UnitPrice) FROM Sales.OrderLines WHERE OrderId = @OrderId 
 
RETURN @Total 
END 

We used this function in the following query and gathered the execution stats via Plan Explorer:

SELECT o.OrderId, o.OrderDate, Sales.CalculateOrderLineTotal(o.OrderId) AS OrderTotal 
FROM Sales.Orders o 
ORDER BY o.OrderDate ASC 

This resulted in the following outcomes, comparing:

1. A base query without the order total
2. A query using the new UDF to return the order total
3. A final query with the contents of the UDF inlined as a correlated subquery into the main query

Inlining Scalar UDFs in SQL Server 2019

SQL Server 2019 introduced improvements that potentially allow scalar UDFs to be automatically inlined into the calling query, effectively doing some of the work for us that we did for query C in the tests above. I say potentially because:

• Not all scalar UDFs can be inlined (we’ll cover some of the common reasons why later on).
• Even if a scalar UDF can be inlined, it doesn’t mean SQL Server will inline it in a given query. And if it does, it still doesn’t mean you’ll end up with the same execution plan and performance as we got with the manual inlining we did.

If you’re using SQL Server 2019 (or later, to those of you in the future), you’ll need to ensure the compatibility level for your database is set to 150 or above.

Do You Even Inline, Bro?

How can you tell if a scalar UDF can be inlined? First, let’s start with some common examples of when they cannot be inlined:

• When it uses time-based functions like GETDATE() and SYSUTCDATETIME()
• When it uses a table variable, table valued parameter, or Common Table Expression (CTE)
• When the scalar UDF is used in an ORDER BY clause

The best way to check is by querying the sys.sql_modules system table which has a bit column (is_inlineable). If that returns 0, then the scalar UDF cannot be inlined.

Using our scenario as an example, the following will check whether we can benefit from automatic inlining in SQL Server 2019 for our UDF:

SELECT * 
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('Sales.CalculateOrderLineTotal')

Great, it returns 1, so it could benefit! Running the query and grabbing the stats for the execution adds D to our previous tests:

We’re now running the exact same query as we did when we originally introduced the scalar UDF into the query (B) that resulted in a huge performance hit. This time, however, performance is noticeably better in terms of Duration, CPU, and Reads. It’s still not as good in this instance when compared to the manual approach to the inline scalar UDF in (C), where all metrics are much lower still. But since the only thing we changed was to switch the database to compatibility level 150 in SQL Server 2019, that’s not too shabby for an out-of-the-box improvement.

If we look at the execution plan now for D, we see:

This looks much more similar to the manually inlined approach. Importantly, we do now have much better visibility of the work the scalar UDF is doing, which we didn’t have at the very start prior to SQL Server 2019/compatibility level 150. But there are some differences compared to the manual approach—we now have a parallel execution plan and a Nested Loop (Inner Join) operation instead of a Hash Match (Right Outer Join) operation for example.

Conclusion

These improvements in SQL Server 2019 could yield some performance improvements when you migrate your database—possibly unexpectedly if you didn’t think you had an issue with scalar UDFs before!

We can never take our eye off the ball when it comes to checking the performance of our queries. It is often the case that there is no silver bullet, and the inlining performed by SQL Server 2019 is very much a case in point. However, as shown above, it’s a welcomed improvement.

Adrian Hills

Adrian (@AdaTheDev) is a Lead Development Engineer at SentryOne, focusing on the development of the Data DevOps SaaS product portfolio – namely SentryOne Document and SentryOne Test. Based in the UK, he joined SentryOne from Pragmatic Works in 2018, with 18 years experience in software development that includes a background in developing SaaS platforms in the hospitality and digital marketing industries. Adrian is a fan of good (bad) puns, dad jokes, and proper British biscuits.