SQL Server 2019 brings a lot of great new features. Many are introduced by the IQP (Intelligent Query Processing) features and greatly improve query performance.

Some time ago I posted about Halloween protection and how using SCHEMABINDING in your Scalar UDFs can eliminate the need for ugly SPOOL operators. You can read more about that here: https://sqltechblog.com/2016/10/31/why-halloween-slowed-your-queries/

Microsoft has a great post on this new feature here:

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15

As for the Halloween protection, let’s take a quick look at how this behaves in SQL Server 2019.

As a quick refresher SQL Server 2017 and older behave like this without using SCHEMABINDING.

Note the first query, which uses the scalar UDF, is higher cost due to the SPOOL.

If we run this in SQL Server 2019 using the 150 compatibility mode, what happens?

Woohoo! The SPOOL is gone without having to alter the code and anything that improves performance without code changes is amazing!

Gotchas

As you may have guessed. UDF inlining is quite new and will be improving over time as the feature matures. You may note that there are hotfixes that have been released to fix some bugs related to this new feature.  (https://support.microsoft.com/en-us/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019)

As of today, SQL Server 2019 CU4 is the latest and it would seem there’s still some work to be done.

The following code block seems to reproduce a bug in the current latest:

CREATE DATABASE ReproBug;
GO
USE ReproBug
GO
CREATE TABLE Contact (id int identity(1,1), fname varchar(50), lname varchar(50));
GO

USE ReproBug
GO
CREATE OR ALTER PROCEDURE [dbo].[proc_InsertContact]
(
     @lastName     VARCHAR(100),
     @firstName     VARCHAR(100)
)
AS
BEGIN
     BEGIN TRAN

    BEGIN TRY
         INSERT INTO Contact (fname, lname) values (@lastName, @firstName);

        COMMIT
         return 0;
     END TRY
     BEGIN CATCH 
         ROLLBACK TRAN
         RETURN dbo.fx_GetErrorNumber(Error_number());
     END CATCH
END
GO

CREATE OR ALTER FUNCTION [dbo].[fx_GetErrorNumber] (@errorCode INT = NULL)
RETURNS INT  AS 
BEGIN

DECLARE @retValue INT
SET @retValue = 500

IF(@errorCode = 2627)
     SET @retValue = 501   

RETURN @retValue
END
GO

exec dbo.[proc_InsertContact] @lastName=’Vader’,@firstName=’Darth’;

Msg 596, Level 21, State 1, Line 43
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 43
A severe error occurred on the current command.  The results, if any, should be discarded.

Is there a work-around?

Yes; you can set the compatibility mode to 140 or change the UDF to disable inlining. Either change the UDF itself with “WITH INLINE = OFF” or update the database to disable inlining “ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;”

This is an issue I’m watching and I’ll follow up on this post when this issue is resolved.

Update! 

SQL Server 2019 CU5 has been released and resolves the issue described above. Thanks go to the team for their hard work on continuously improving the product. You can find SQL Server 2019 CU5 here:

https://support.microsoft.com/en-us/help/4552255/cumulative-update-5-for-sql-server-2019