1

Something has changed in functions from SQL Server 2016 to version 2019.

This is the test function:

create function [dbo].[TestFunction]
(
    @input uniqueidentifier
)
returns uniqueidentifier
as
begin
    select top 0 @input = id from randomTable;

    return
    (
        select  @input
    )
end

If I try this code in SQL Server 2016, the result is the input value, but in SQL Server 2019, the result is null.

Can someone point me to the right direction what has changed?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Radic
  • 41
  • 8
  • 4
    Guessing this is due to the inlining functionality introduced in SQL Server 2019. Why would you *have* a query like this though? What purpose does `TOP (0)` serve? This feels like a question, that although interesting, isn't a problem; because why *would* you ever need to top 0 rows? – Thom A Jan 10 '20 at 15:45
  • 1
    @Larnu [This answer explains a couple of scenarios for using TOP 0](https://stackoverflow.com/questions/6033957/sql-server-select-top-0/6033976), although I'm less sure why you'd try and do a variable assignment in the same query – Diado Jan 10 '20 at 15:49
  • 1
    Yep, using it with a `UNION ALL` makes sense @Diado ; though I see it as an odd work around to just aliasing in the correct places. But that doesn't explain the variable, but you agreed on that too. This just feels like a non-question. – Thom A Jan 10 '20 at 16:12
  • 2
    See [scalar UDF inlining](https://learn.microsoft.com/sql/relational-databases/user-defined-functions/scalar-udf-inlining) and [interleaved execution](https://learn.microsoft.com/sql/relational-databases/performance/intelligent-query-processing#interleaved-execution-for-mstvfs), which describes why these behavioral changes happen and how to tweak it either for the whole database or per query if you can't change your existing code to be more sensible (the latter should be preferred, because these optimizations are generally good things). – Jeroen Mostert Jan 10 '20 at 16:55
  • I have used this query just to explain my problem, what is happening when you don't have any results. In production I am not using this. – Radic Jan 13 '20 at 13:18
  • This was a good question and I dont really see why people felt the need to pick apart the example bit of code presented, it was obviously just a minimal example to demonstrate the behaviour change. – codeulike Mar 29 '21 at 08:48

1 Answers1

2

The returned NULL is due to scalar udf inlining:

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

if you want the function to take advantage of inlining and return correct results you could refactor it to :

create function [dbo].[TestFunction]
(
    @input uniqueidentifier
)
returns uniqueidentifier
--WITH INLINE = ON
as
begin
    --select top (0) @input= id from randomTable

    return
    (
        isnull((select top (0) id from randomtable), @input)
    )
end
lptr
  • 1
  • 2
  • 6
  • 16