en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
מרץ17

Written by: ronen ariely
17/03/2021 21:29 RssIcon

background

This issue was reported in the Microsoft QnA forum! Using nested User-defined Scalar Functions in SQL Server version 15.0.4102.2 returns an error. I confirmed this issue in several machines.

This issue was first (as much as I know this was first) reported by Stefan in this thread:

https://docs.microsoft.com/en-us/answers/questions/318695/sql-server-2019-cu9-scalar-udf-inlining-issue.html

Reproduce the issue the issue

Let's create 3 nested Inline User-defined Scalar Functions

CREATE FUNCTION [dbo].[GetSub1] (@param NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
    RETURN (@param + ' Test2')
END
GO
     
CREATE FUNCTION [dbo].[GetSub2] ()
RETURNS NVARCHAR(4000)
AS
BEGIN
    RETURN (
            SELECT TOP 1 name
            FROM [sys].[columns]
            )
END
GO
     
CREATE FUNCTION [dbo].[GetMain1] ()
RETURNS NVARCHAR(4000)
AS
BEGIN
    RETURN (dbo.GetSub1(dbo.GetSub2()))
END
GO


Now when we execute the GetMain1 function if you are using the last (at this time) SQL Server version which is 15.0.4102.2 then you will get an error

SELECT [dbo].[GetMain1] ()
GO


Msg 596, Level 21, State 1, Line 29
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 29
A severe error occurred on the current command.  The results, if any, should be discarded.

Note! This issue does not exists in previous version 15.0.4083.2

Tests and confirmation

First I tested on production machine which had a working instance version CU9 (15.0.4102.2) and I confirm that I get the same issue.


Secondly I started with a machine which include multiple versions of SQL Server instances from 2014 to 2019. The instance of 2019 was CU8 (15.0.4083.2). No issue appeared. I updated the server to the last version and the issue raised.

Note! Since this appeared in the last version and usually Azure SQL Database uses the latest version, then I decided to test in Azure SQL Database and I can confirm that this is NOT an issue in the Azure. This issue exists only in SQL Server on-premises version CU9 (15.0.4102.2)

Work around

This issue is definitely related to to

If you have to use this version and you must use nested inline User-defined Scalar Functions, then you should NOT use inline functionality.

Option 1: Disable the inline functionality of the function using "WITH INLINE = OFF" 

Option 2: Instead of using inline function use multi statement function

Obviously this mean that we lose the new advantages in using inline User-defined Scalar Functions, which I love so much.

What Next?

I sent email to one of the SQL Server developers and I asked the user in the fogram to open the official bug report so he will get the credit on reporting this first. I publish this issue here for the sake of my clients and readers, but again, this issue was not reported by me. I only helped to confirm it :-)

I will provide more information once I will get...