带SCHEMABINDING的UDF确定性属性未按预期工作

问题根源：对SQL Server确定性UDF的定义误解 #

你混淆了SQL Server中「确定性函数」的实际含义：

• SQL Server判定的确定性函数，指的是相同输入参数 + 依赖的底层数据状态不变时，返回结果完全一致；一旦依赖的数据发生变更，函数会返回基于新数据的结果。
• WITH SCHEMABINDING的作用是锁定函数依赖的表结构，防止表被修改导致函数失效，不是用来缓存函数返回值的。

所以你看到更新表后UDF返回新值，是符合SQL Server确定性函数定义的正常行为，并非异常。

解决需求：同一输入避免重复执行函数逻辑 #

针对你提到的「查询大表返回逗号分隔字符串集合」的场景，推荐以下几种方案：

方案1：手动实现缓存表

创建专门的缓存表存储函数输入与结果的映射，每次调用时优先读取缓存，无缓存时再执行逻辑并写入缓存，同时处理数据更新时的缓存失效。

示例代码：

-- 创建缓存表
CREATE TABLE dbo.udf_cache
(
    input_param INT PRIMARY KEY, -- 替换为你的UDF输入参数类型
    result VARCHAR(MAX),
    last_updated DATETIME2 DEFAULT GETDATE()
);

-- 创建带缓存逻辑的存储过程（替代UDF，UDF内不允许写操作）
CREATE OR ALTER PROCEDURE dbo.get_comma_separated_value
    @input_param INT
AS
BEGIN
    SET NOCOUNT ON;
    -- 优先读取缓存
    SELECT result FROM dbo.udf_cache WHERE input_param = @input_param;

    IF @@ROWCOUNT = 0
    BEGIN
        -- 无缓存时执行原逻辑（替换为你的逗号分隔字符串生成逻辑）
        DECLARE @result VARCHAR(MAX);
        SELECT @result = STRING_AGG(column_name, ',') 
        FROM dbo.large_table WHERE id = @input_param;

        -- 写入缓存
        INSERT INTO dbo.udf_cache(input_param, result)
        VALUES (@input_param, @result);

        -- 返回结果
        SELECT @result AS result;
    END
END
GO

-- 缓存失效触发器：底层大表更新时清空对应缓存
CREATE OR ALTER TRIGGER trg_large_table_update
ON dbo.large_table
AFTER UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM dbo.udf_cache
    WHERE input_param IN (SELECT id FROM deleted);
END
GO

方案2：改用内联表值函数（iTVF）+ 应用层缓存

标量UDF性能较差，改用内联表值函数可提升执行效率，同时在应用层实现缓存（如Redis、内存字典），避免同一输入重复调用数据库。

示例内联表值函数：

CREATE OR ALTER FUNCTION dbo.itvf_get_comma_separated_value
(
    @input_param INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT STRING_AGG(column_name, ',') AS result
    FROM dbo.large_table
    WHERE id = @input_param
);
GO

应用层调用时，对相同的@input_param值先检查本地缓存，不存在时再调用该函数并将结果存入缓存。

方案3：内存优化表缓存（高并发场景）

如果需要纯数据库层面的高效缓存，可使用内存优化表存储缓存数据，大幅提升读取速度：

CREATE TABLE dbo.udf_memory_cache
(
    input_param INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
    result VARCHAR(MAX),
    last_updated DATETIME2 DEFAULT GETDATE()
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

后续逻辑与方案1一致，仅缓存表替换为内存优化版本。

内容的提问来源于stack exchange，提问作者Iniyavan