7

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?

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
pelod
  • 73
  • 1
  • 5
  • original code performs select @v = ... from some_table where.... I just simplified it. Table select worked wrong, too. – pelod Nov 20 '19 at 11:23

2 Answers2

6

Scalar udf inlining yet again, rather buggy

SELECT dbo.rowcount_test()

OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));
lptr
  • 1
  • 2
  • 6
  • 16
  • 4
    Whether this is "buggy" or not is debatable, but it *is* [documented](https://learn.microsoft.com/sql/relational-databases/user-defined-functions/scalar-udf-inlining), and hence probably by design. "If a UDF references built-in functions such as `SCOPE_IDENTITY()`, `@@ROWCOUNT`, or `@@ERROR`, the value returned by the built-in function will change with inlining. This change in behavior is because inlining changes the scope of statements inside the UDF." They have given plenty of knobs to tweak the inlining (including turning it off for the entire database), so not a major issue. – Jeroen Mostert Nov 20 '19 at 11:23
  • 2
    To whoever downvoted this: it's easy to verify that [the answer is correct](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=58c785cc8e59d1d037368fc50011b749). Compare execution plans with and without inlining by commenting out the `OPTION`. – Jeroen Mostert Nov 20 '19 at 11:31
  • thanks, guys, great help, now I know what it is and how to deal with it. The problem was it was a 1st time my code got confronted with mssql 2019 because customer ignored our sys reqs so I had to fix it on the fly. – pelod Nov 20 '19 at 11:38
4

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
Journey
  • 1,023
  • 1
  • 9
  • 12