I recently published a post detailing the new Scalar UDF Inlining feature in SQL 2019 here. That post introduced the new feature in a way that I used to compare performance to the other function types, continuing the performance evaluation of functions that I had previously posted here and here. In the Scalar UDF Inlining post, I used a function to strip all non-numeral values from a string, and to return the result. This used the FOR XML output option.

In thinking about how scalar functions are commonly used, I’ve decided to revisit this feature with a simpler function. I will still compare it to all the other types of functions to see how Scalar UDF Inlining compares to the others.

Scalar UDF Inlining Recap

Scalar UDF Inlining takes the operations that the Scalar UDF performs, and inlines those operations into the query plan, similar to a view or an Inline Table-Valued Function. To be able to inline the function, there are some requirements (see the link in the first sentence of this paragraph) that the function must meet.

The Test Environment

As I stated before, I want to run this test with a simpler function. I decided to have the function accept a number, and to return the number multiplied by itself. As in the previous test, we’ll use two databases (one in SQL 2019 compatibility mode, and the other in SQL 2017 compatibility mode):

Inside each database, I’ve created a tally table, and three functions (one Scalar, one Inline Table-Valued Function (iTVF) and one Multi-Statement Table-Valued Function (MSTVF)):

With these functions created, we run a simple test to see that they work properly:

This returns the following result set:

Function test results

And the following execution plan (SQL 2017):

Query Plan

Notice that there is only one “Compute Scalar” operator that handles both the Scalar UDF and the iTVF. Inside this operator, all of the scalar operations that can be performed at this level are performed. Let’s look at this operator’s properties:

Compute Scalar Properties

Expr1001 is:

Compute Scalar Expr1001

And Expr1002 is:

Compute Scalar Expr1002

Be reviewing these, we can see that Expr1001 is for the iTVF, and Expr1002 is for the Scalar function. Even though they can both be pulled into the execution plan into the same query operator, they are doing different things. We can now continue on to…

The Performance Test

Just like in the last post, we’ll run each function individually against the tally table, dumping the results into a temp table. The test against each function is run 10 times. The testing query is:

The SQL 2017 results are:

SQL 2017 Function Test Results

The SQL 2019 results are:

SQL 2019 Function Test Results

Wow, the scalar function performance has improved so well that the times are essentially a tie. The average shows just how close they are. The scalar function even beat out the iTVF several times (highlighted in yellow above).

In conclusion…

In my last post, I concluded that the iTVF was still a bit faster, and recommended still using that. With this post, my recommendation is that for simple Scalar UDFs, it may be enough. For more involved functions, it requires some testing to determine which implementation would be better. This testing is to determine if the Scalar UDF can run as good as the iTVF. You can avoid this testing by just using an iTVF if possible. If you have an existing application, Scalar UDF Inlining will improve the performance just by being in the SQL 2019 compatibility level.

As all of these function tests have shown, only use Multi-Statement Table-Valued Functions if you can’t do it in another way.


LondonDBA noted in the comments below that I had a copy / paste error in my test, specifically when dealing with the scalar function. That is the highlighted line in the above testing code (it was not remarked out). Since this issue could affect the performance in both databases, I’ve rerun the testing.

SQL 2017 results:

Updated SQL 2017 results

SQL 2019 results:

Updated SQL 2019 results

With this test, the scalar UDF is, on average, performing ever-so-slightly better than the iTVF. We can also see that in 7 of the 10 runs, it ran the fastest. Scalar UDF Inlining is definitely a game changer when considering what type of function to use. You do still need due diligence to test the functions to ensure that it performs the best for you.