Old SQL function using @@ROWCOUNT causing issues-CodePudding

We have a SQL function which was written years ago that has been causing sporadic issues (for years I think).

I was finally able to get my hands on a backup of a customers DB so that I could figure out what is going on.

The function in question pulls the latest "session" ID from the DB and returns it unless @@ROWCOUNT is equal to 0, in which case it returns an empty string.

ALTER FUNCTION [dbo].[GetCurrentSessionId]()
RETURNS nvarchar(255)
AS
BEGIN
    declare @v nvarchar(255)

    select top 1 @v = SessionId
    from RestoreSession
    where 
        SessionState <> 3 and -- Complete
        SessionState <> 7 -- CompletedWithWarnings
    order by SessionId desc

    if @@ROWCOUNT = 0
        set @v = ''

    return @v
END

Every once in a blue moon this function suddenly will start always returning an empty string even when there is a session in the DB.

When I looked at the above function for the first time while debugging the issue I noticed the @@ROWCOUNT and thought that it was odd.

I removed the @@ROWCOUNT condition and retested, this time the session ID was correctly returned. I then re-added the @@ROWCOUNT condition and retested and it once again returns an empty string.

Further details which may or may not matter. This function is being called from a C# application using the utilities in the System.Data.SqlClient namespace.

The method which calls the above SQL function looks like the following.

public string GetCurrentSessionID()
{
    string res = string.Empty;
    using (var connection = _persistence.CreateSqlConnection())
    {
        var sessionsQuery = "select [dbo].GetCurrentSessionId() as SID";
        using (var sqlCommand = connection.CreateCommand(sessionsQuery))
        {
            using (var sessionReader = sqlCommand.ExecuteReader())
            {
                if (sessionReader.Read())
                {
                    if (!sessionReader.IsDBNull("SID"))
                    {
                        res = sessionReader.GetString("SID");
                    }
                }
            }
        }
    }
    return res;
}

In the above C# method both IsDBNull and GetString are custom extension methods that look like the following:

public static bool IsDBNull(this IDataRecord reader, string name)
{
    return reader.IsDBNull(reader.GetOrdinal(name));
}

public static string GetString(this IDataRecord reader, string name)
{
    return reader.GetString(reader.GetOrdinal(name));
}

I also want to mention that when I execute the SQL function from within SSMS without the removal of the @@ROWCOUNT conditional statement the session ID is correctly returned.

This only seems to occur when calling the function from the C# application.

I believe the fix is removing the @@ROWCOUNT conditional statement as when I did that the C# application was able to correctly pull the session ID.

I'm curious what the issue could be in this case? Why does the @@ROWCOUNT seem to "not work" sporadically when the function is called from the C# application.

CodePudding user response：

The problem here is that you are using completely unpatched version of SQL Server 2019 and inlining of user scalar functions, and also expecting the same behaviour from other versions of SQL Server (such as those prior to 2019, where scalar inlining didn't exist) or newer versions of 2019, where the problem you are experiencing has been addressed.

Firstly, the problem. If we look at Inlineable scalar UDF requirements you'll note that it states:

Inlineable scalar UDF requirements

A scalar T-SQL UDF can be inlined if all of the following conditions are true:

...

The UDF doesn't contain references to intrinsic functions that may alter the > results when inlined (such as @@ROWCOUNT) ⁴.

...

...
⁴ Restriction added in SQL Server 2019 (15.x) CU2

Notice that this restriction was added in CU2; this is because it was noticed early on that inlining functions, such as @@ROWVERSION was causing unexpected/undesired results, and so Microsoft addressed this by making it so that functions using said functions don't inline. As such the function returns back to being a multi-line scalar function.

As you are using 2019 RTM, you don't have this update, and so the function is being inlined and resulting in known undesired behaviour; just one reason of many as to why you should be keeping your SQL Server up to date.

As, however, you are using a range of versions for your deployments, I honestly would suggest that you steer clear of user defined scalar functions for a couple of reasons (unless they are very simple, and even then...). Multi-line scalar function can perform poorly, and inline table value functions (iTVF) are often far more performant. Also, their behaviour doesn't change from version to version (or more specifically from an unpatched to patched version of 2019), so you know that things won't change in a multi client environment, who are using different versions (and some clients are truly useless at updating their servers).

The above query can actually easily be rewritten as a iTVF as the following:

CREATE FUNCTION dbo.GetCurrentSessionID()
RETURNS table
AS RETURN
    SELECT ISNULL((SELECT TOP (1) SessionId
                   FROM dbo.RestoreSession
                   WHERE SessionState <> 3 -- Complete
                     AND SessionState <> 7 -- CompletedWithWarnings
                   ORDER BY SessionID DESC),'') AS SessionID;

If you must use an inline scalar function, then I would suggest that due to having a multi-client environment, with different versions, you ensure that scalar inlining is turned off. This would probably be best at the database level, and thus you should ensure that on creation, for instances on 2019 , you have the following configuration set, by running it on the specific database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Though you could do this at the instance level, your clients may not thank you if they are using inlining else where on their instance.

Alternatively, you can set it at the function's level, using WITH INLINE = OFF, on instances running 2019 :

ALTER FUNCTION [dbo].[GetCurrentSessionId]()
RETURNS nvarchar(255)
WITH INLINE = OFF
AS
BEGIN
    declare @v nvarchar(255);

    select top 1 @v = SessionId
    from RestoreSession
    where 
        SessionState <> 3 and -- Complete
        SessionState <> 7 -- CompletedWithWarnings
    order by SessionId desc;

    if @@ROWCOUNT = 0
        set @v = '';

    return @v;
END;

Though, if I am honest, you could also just make it so that the query doesn't use @@ROWCOUNT and has a single RETURN value:

CREATE FUNCTION [dbo].[GetCurrentSessionId]()
RETURNS nvarchar(255)
AS
BEGIN
    RETURN (SELECT ISNULL((SELECT TOP (1) SessionId
                   FROM dbo.RestoreSession
                   WHERE SessionState <> 3 -- Complete
                     AND SessionState <> 7 -- CompletedWithWarnings
                   ORDER BY SessionID DESC),''));
END;

But, again, I personally suggest switching to an iTVF for better performance across all environments (not just environments using SQL Server 2019 ).