Error in @@ROWCOUNT inside SQL Server 2019 UDF

Question

I found the @@ROWCOUNT when it is used inside a User Defined Function in MS SQL Server 2019 it doesn't work properly any more.

I don't know if it's a MS SQL bug or a desired change of behavior.

There is any option to go back to the behavior of 2019 an previous versions? Is it something about the setup of the installation?

Here is the code and results for your own test:

CREATE FUNCTION [dbo].[udfScalar](@test NVARCHAR(255)) RETURNS int
AS BEGIN
    DECLARE @var AS NVARCHAR(255)
    DECLARE @ROWCOUNT AS INT = NULL
    select top 1 @var = 'test' from FooTable where 1 = 0
    SET @ROWCOUNT = @@ROWCOUNT
    RETURN @ROWCOUNT;
END
GO
    DECLARE @var AS NVARCHAR(255)
    DECLARE @ROWCOUNT AS INT = NULL
    select top 1 @var = 'test' from FooTable where 1 = 0
    SET @ROWCOUNT = @@ROWCOUNT

select @ROWCOUNT '@@ROWCOUNT in T-SQL', [dbo].[udfScalar]('test') '@@ROWCOUNT in UDF', @@VERSION 'MS SQL VERSION'
drop FUNCTION [dbo].[udfScalar]

Result in MS SQL Server 2019 (wrong):

@@ROWCOUNT in T-SQL: 0
@@ROWCOUNT in UDF:   1
MS SQL VERSION: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 
    Sep 24 2019 13:48:23 
    Copyright (C) 2019 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Note that UDF returns 1 when the the result should be 0 because of the where 1 = 0.

Result in MS SQL Server 2016 and previous versions (correct):

@@ROWCOUNT in T-SQL: 0
@@ROWCOUNT in UDF:   0
MS SQL VERSION: Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) 
    Jun 15 2019 23:15:58 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Right now I don't have access to SQL Server 2017 but I believe it was correct.

Answer 1

@Larnu answer is correct, this change of behavior is due to the scalar inlining.

Adding the modifier WITH INLINE = OFF works as expected.

CREATE FUNCTION [dbo].[udfScalar](@test NVARCHAR(255)) RETURNS int
WITH INLINE = OFF
AS BEGIN
    DECLARE @var AS NVARCHAR(255)
    DECLARE @ROWCOUNT AS INT = NULL
    select top 1 @var = 'test' from FooTable where 1 = 0
    SET @ROWCOUNT = @@ROWCOUNT
    RETURN @ROWCOUNT;
END