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.

I suspect this is a case of scalar inlining. If you use `WITH INLINE = OFF` (See [Disabling Scalar UDF Inlining without changing the compatibility level](https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15#disabling-scalar-udf-inlining-without-changing-the-compatibility-level)) in the function's declaration do you get the correct results? Ideally, you want to avoid user scalar functions, as they are known to perform poorly. — Thom A, Feb 12 '20 at 10:55
This does seem like a non-question though. Why would you ever *need* to count the number of rows where `1 = 0` and *why* use `@@ROWCOUNT` when you have a perfectly good `COUNT` function to achieve this. This is a scenario that would never exist in the "real world" so why does the behaviour matter? — Thom A, Feb 12 '20 at 10:58
@Larnu The `1=0` is a silly example to demonstrate that `@@ROWCOUNT = 1` it isn't the expected value. — Alex, Feb 12 '20 at 11:17

score 3 · Answer 1 · edited May 16 '20 at 00:06

This is a know bug related to the UDF inlining. UDF inlining was added with SQL Server 2019 in order to improve the performance of UDFs. You need to install the latest CU infact starting from SQL Server 2019 CU2. If a UDF contains @@ROWCOUNT, the UDF will not be inlined anymore. Otherwise, you could add the option WITH INLINE = OFF.

For more information:

Alex · Accepted Answer · 2020-02-12T11:35:57.833

@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

Error in @@ROWCOUNT inside SQL Server 2019 UDF

2 Answers2