sql-server - SQL CLR 标量函数缓存结果

Question

一般问题

迁移到 SQL Server 2014 后，以前在 SQL Server 2008 R2 中每次执行都会产生不同结果的 CLR 标量函数的执行，现在会为每次后续调用产生相同的结果。

测试/说明

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;

我们目前正在将环境从 SQL Server 2008 R2 升级到 SQL Server 2014。我们遇到的问题之一是特定于 CLR 标量函数的执行。功能比较简单；它根据刻度数生成一个内部 ID(我们不要争论此函数的需要/目的)。

在 SQL Server 2008 R2 环境中，循环执行该函数会返回预期结果(每次执行都会产生一个新值)。但是，在我们的新 SQL Server 2014 环境中进行测试时，相同的代码为循环中的每次执行返回相同的值。

SQL Server 2008 R2 中的结果:

20161213502167209995
20161213502168210095
20161213502169210195
20161213502170210295
20161213502171210395
20161213502172210495
20161213502173210595
20161213502174210695
20161213502175210795
20161213502176210895

SQL Server 2014 中的结果:

20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787

我们使用此代码为我们收到的特定类型的每个文件生成特定 ID。为每个文件生成一个新值，但处理此问题的 SSIS 进程通常需要 4-5 秒来处理每个文件。因此，这个函数每隔几秒就会从完全不同的连接调用一次，但我们仍然得到重复的结果。

返回值确实会周期性变化，所以并不是说我们整天只返回一个值，而是在一段时间内，无论是 3 秒还是 30 秒，函数都会返回相同的结果。

我们缺少什么？

CLR 函数定义

[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

从 SQL Server 2012 开始，这是预期行为。

问题是您已通过 SqlFunction 属性指定此函数应被视为“确定性”:

IsDeterministic = true

确定性函数对于相同的输入值返回相同的值。考虑到您的函数没有任何输入参数，因此它的所有执行本质上都是相同的。

对于真正确定性的函数来说，这种行为可以带来巨大的性能提升。但是，由于您的函数应该在每次执行时返回不同的值，因此它实际上并不是确定性的。

要解决此问题，您应该能够设置 IsDeterministic = false，或者直接删除 IsDeterministic = true，因为 IsDeterministic 的默认值code> 为 false。

附注我知道你说过不要争论为什么你有这个特定的功能，但只是提一下，以防万一你(或其他人)不知道它，当前的刻度值应该在 DMV 中公开:

SELECT [cpu_ticks] FROM sys.dm_os_sys_info;

P.P.S。最好使用 Sql* 类型作为输入参数和返回值。意思是，使用 SqlString 而不是 String 作为返回类型。这可能需要添加 using System.Data.SqlTypes;。