SQL CLR Scalar Function cached results

Question

General Problem

After moving to SQL Server 2014, execution of a CLR scalar function which previously produced different results for each execution in SQL Server 2008 R2, now produces identical results for each subsequent call.

Test/Illustration

DECLARE @i int = 0;
WHILE @i < 10
 BEGIN
    print dbo.getEligLoadTXID();
    --> Simulate delay as this code never gets called more than once every few seconds in our environment
    WAITFOR DELAY '00:00:00.1';
    SET @i += 1;
 END;

We are currently in the process of upgrading our environment from SQL Server 2008 R2 to SQL Server 2014. One of the problems we have run into is specific to the execution of a CLR scalar function. The function is relatively simple; it is generating an internal ID based on the number of ticks (let's not argue about the need/purpose for this function).

In a SQL Server 2008 R2 environment, executing the function in a loop returns expected results (each execution results in a new value). However, when testing in our new SQL Server 2014 environment, the same code is returning the SAME value for each execution in the loop.

Results in SQL Server 2008 R2:

20161213502167209995
20161213502168210095
20161213502169210195
20161213502170210295
20161213502171210395
20161213502172210495
20161213502173210595
20161213502174210695
20161213502175210795
20161213502176210895

Results in SQL Server 2014:

20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787

We are using this code to generate a specific ID for each file that we receive of a specific type. A New value gets generated for every file, but the SSIS process that handles this typically takes 4-5 seconds to process per file. So, this function is being called once every few seconds, from completely different connections, but we are still getting duplicate results back.

The returned value does change periodically, so its not like we only get a single value back all day long, but for periods of time, whether it be 3 seconds or 30 seconds, the function will return the same result.

What are we missing?

CLR Function Def

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static String getTransactionID()
{
    DateTime NOW = DateTime.Now;

    long ticks = NOW.Ticks - new DateTime(NOW.Year, NOW.Month, NOW.Day, 0, 0, 0, 0).Ticks;

    return String.Format("{0:0000}{1:00}{2:00}{3}", NOW.Year, NOW.Month, NOW.Day, ticks.ToString());
}

Answer 1

This is expected behavior, starting with SQL Server 2012.

The issue is that you have specified, via the SqlFunction attribute, that this function should be treated as "Deterministic":

IsDeterministic = true

A deterministic function returns the same value for the same input values. And considering that your function does not have any input parameters, there all executions of it are essentially the same.

This behavior is a huge performance gain for functions that are truly deterministic. However, since your function is supposed to return a different value per each execution, it is not actually deterministic.

To fix this, you should be able to set IsDeterministic = false, or simply remove the IsDeterministic = true, since the default value for IsDeterministic is false.

P.S. I know you said not to argue about why you have this particular function, but just to mention in case you (or others) were not aware of it, the current tick value should be exposed in a DMV:

SELECT [cpu_ticks] FROM sys.dm_os_sys_info;

P.P.S. It is also best to use the Sql* types for input parameters and return values. Meaning, use SqlString instead of String for the return type. This might require adding using System.Data.SqlTypes;.