This article is just a simple demonstration of improvements in scalar functions handling implemented in SQL Server 2019. Prior to this version when the scalar function was used in a query, it was blocking parallel plan generation. You can read more on this topic in this article. One of the most anticipated features in SQL Server 2019 is the Scalar UDF Inlining and I will show you a very simple practical demonstration of how this feature can improve query performance compared to SQL 2017 and older versions.
We will build a scalar function that will replace two HTML tags in the input string with string literals used for tags escaping. We will also build the same function as Inlined because that solution was used before SQL 2019 to address most of the scalar functions related issues. Table dbo.SamleTable will be filled with 100k records to be used in this test.
USE [tempdb] GO -- Scalar Function CREATE FUNCTION [dbo].[f_Scalar_ReplaceHTMLTags]( @Data NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) BEGIN RETURN REPLACE(REPLACE(@Data, '<', '<'), '>', '>') END GO -- Inlined function CREATE FUNCTION [dbo].[f_Inline_ReplaceHTMLTags]( @Data NVARCHAR(MAX) ) RETURNS TABLE AS RETURN (SELECT REPLACE(REPLACE(@Data, '<', '<'), '>', '>') [Data]) GO -- Sample data with 100k records CREATE TABLE [dbo].[SampleTable] ( [Data] NVARCHAR(MAX) ) INSERT INTO [dbo].[SampleTable] ( [Data] ) SELECT TOP 100000 REPLICATE('<' + 'HTML_Tag_Name' + '>', 100) FROM [msdb].[sys].[columns] [c1] CROSS JOIN [msdb].[sys].[columns] [c2] GO
Let’s run two queries against the sample data and compare it between SQL Server 2019 and 2017 and review execution plans generated and query statistics. The first query will use the scalar function, the second one is using CROSS APPLY with the inlined function.
SET STATISTICS TIME ON SET STATISTICS IO ON GO SELECT [dbo].[f_Scalar_ReplaceHTMLTags]([Data]) [ReplacedData] INTO [#tmp1] FROM [dbo].[SampleTable] GO SELECT [f].[Data] [ReplacedData] INTO [#tmp2] FROM [dbo].[SampleTable] [st] CROSS APPLY [dbo].[f_Inline_ReplaceHTMLTags]([st].[Data]) [f] GO
CPU time = 44797 ms, elapsed time = 55845 ms. Table ‘SampleTable’. Scan count 5, logical reads 50000.
CPU time = 41189 ms, elapsed time = 10511 ms. Table ‘SampleTable’. Scan count 5, logical reads 50000.
CPU time = 34171 ms, elapsed time = 12179 ms. Table ‘SampleTable’. Scan count 5, logical reads 50000.
CPU time = 33828 ms, elapsed time = 15719 ms. Table ‘SampleTable’. Scan count 5, logical reads 50000
As we can see the only difference in execution plans is that in 2017 scalar function was blocking optimizer from parallel plan creation. We can get rid of this issue when an inlined version of the function will be used. This workaround is not necessary for then SQL Server 2019 where the scalar function was successfully inlined and the same parallel plan was generated for both queries (scalar and inlined).