Forum Discussion

KanishkaB's avatar
KanishkaB
Copper Contributor
Nov 01, 2023

SQL Server Scalar UDF inline

I m currently using the below SQL Server 2019 with compatibility 150

Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64) Jan 23 2023 12:37:13 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

 

Seeing a weird behavior with one of the functions where its returning a different result set with 150 mode on. However if the function is modified with INLINE OFF or if it is called with hint OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING')) its returning the correct result.


Did anyone face any similar issue with this version. I have come across issues that describe problems with UDF previously so wanted to check if anyone faced anything like this with this version. 

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Nov 02, 2023
    You haven't mentioned what the UDF do and how the "different results" look like, so difficult to guess nor do we know how to reproduce.
    You have to provide more details.
    • KanishkaB's avatar
      KanishkaB
      Copper Contributor
      Nov 07, 2023
      hi, here is a demo version of the function and the sample call to it with output

      -- function used to get value for a key
      CREATE FUNCTION [dbo].[fn_ForDemo] (@mSettings NVARCHAR(1000),
      @mKey NVARCHAR(100))
      RETURNS NVARCHAR(100)
      AS
      BEGIN
      DECLARE @mValue NVARCHAR(100)
      DECLARE @mKeyValue NVARCHAR(200)

      IF @mSettings IS NULL OR @mKey IS NULL
      BEGIN
      SET @mValue = NULL
      END
      ELSE
      BEGIN
      SELECT @mKeyValue = VALUE
      FROM STRING_SPLIT(@mSettings, ';')
      WHERE VALUE LIKE '%' + @mKey + '|:|%'

      SELECT @mValue = Value
      FROM STRING_SPLIT(@mKeyValue, ':')

      IF @mValue LIKE '|%'
      SET @mValue = SUBSTRING(@mValue, 2, LEN(@mValue) - 1)

      IF @mValue LIKE '%|'
      SET @mValue = SUBSTRING(@mValue, 1, LEN(@mValue) - 1)
      END

      RETURN COALESCE(@mValue, '')
      END
      GO

      SELECT dbo.fn_ForDemo('abc|:|123|;|def|:|345|;|ghy|:|678|;|jkl|:|999|;|mno|:|888'
      ,'jkl')
      --output= jkl(incorrect)


      SELECT dbo.fn_ForDemo('abc|:|123|;|def|:|345|;|ghy|:|678|;|jkl|:|999|;|mno|:|888'
      ,'jkl')
      OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))
      --output= 999 (correct)

Resources