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:
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.