CODEWITHSUNDEEP
sql-servert-sqluser-defined-functionsrowcountsql-server-2019
pelod
pelod

Reputation: 73

Unexpected @@rowcount behavior inside an UDF in MS SQL 2019

here's my sample code

drop function rowcount_test
go
CREATE FUNCTION dbo.rowcount_test () RETURNS INT AS
BEGIN
    DECLARE @v INT
    SELECT @v = 1
    return @@ROWCOUNT
END
GO
grant exec on dbo.rowcount_test to public
go
SELECT dbo.rowcount_test()

It gives 1 when executed by mssql 2017 (and earlier)

It gives 0 when executed by mssql 2019

It gives 1 when executed by mssql 2019 (Standard edition) with a db put to the 2017 compatibility mode

It's never been a problem before... Is it a kind of setting affecting the code or a kind of bug in MSSQL 2019?

Upvotes: 7

Views: 3932

Answers (2)

lptr
lptr

Reputation: 6788

Scalar udf inlining yet again, rather buggy

SELECT dbo.rowcount_test()

OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Upvotes: 6

Journey
Journey

Reputation: 1191

This should be fixed now.

https://support.microsoft.com/en-us/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019

This cumulative update also blocks Inlining in the following scenarios:

  • If the UDF references certain intrinsic functions (e.g. @@ROWCOUNT) that may alter the results when Inlined

Upvotes: 3

Related Questions