持久化计算列未使用插入值反而使用默认值的异常问题

问题背景 #

测试SQL Server持久化计算列时发现异常：当定义PersistedId AS ISNULL(ExternalId, UniqueId) PERSISTED，且插入时为ExternalId使用NEWID()生成值，首次查询的PersistedId会与插入的ExternalId值不符；但添加普通非持久化计算列Id AS ISNULL(ExternalId, UniqueId)后，PersistedId值恢复正常。静态GUID插入无此问题，推测是持久化列重复调用了NEWID()。

原因分析 #

NEWID()是非确定性函数，每次调用都会生成新的GUID。SQL Server在处理持久化计算列时，可能对该函数进行了多次求值，导致存储的PersistedId并非插入时ExternalId的实际值。而非持久化计算列仅在查询时求值，会改变查询优化器的执行逻辑，间接让持久化列的求值恢复正常。

解决方案 #

针对插入时需要使用NEWID()的场景，提供以下几种可行方案：

方案1：改用触发器维护目标列 #

放弃持久化计算列，通过INSERT和UPDATE触发器手动维护PersistedId，确保值只计算一次：

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp 
(
    ExternalId UNIQUEIDENTIFIER NULL,
    UniqueId UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID()),
    PersistedId UNIQUEIDENTIFIER NOT NULL
)

-- 插入触发器：替代默认插入逻辑，计算PersistedId
CREATE TRIGGER TR_tmp_Insert ON #tmp
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO #tmp (ExternalId, UniqueId, PersistedId)
    SELECT 
        ExternalId,
        ISNULL(UniqueId, NEWID()),
        ISNULL(ExternalId, ISNULL(UniqueId, NEWID()))
    FROM inserted
END

-- 更新触发器：同步更新PersistedId
CREATE TRIGGER TR_tmp_Update ON #tmp
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE t
    SET 
        ExternalId = i.ExternalId,
        PersistedId = ISNULL(i.ExternalId, t.UniqueId)
    FROM #tmp t
    JOIN inserted i ON t.UniqueId = i.UniqueId
END

-- 测试插入
INSERT INTO #tmp (ExternalId) 
VALUES (null), (NEWID())
SELECT * FROM #tmp

-- 测试更新
UPDATE #tmp
SET externalid = CASE WHEN ExternalId IS NULL THEN newid() ELSE null END
SELECT * FROM #tmp

方案2：保留非持久化计算列作为临时 workaround #

保留那个无实际业务用途的非持久化计算列，利用查询优化器的行为变化修复异常：

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp 
(
    ExternalId UNIQUEIDENTIFIER NULL,
    UniqueId UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID()),
    Id AS ISNULL(ExternalId, UniqueId), -- 保留该非持久化计算列
    PersistedId AS ISNULL(ExternalId, UniqueId) PERSISTED
)

方案3：预生成GUID再插入 #

插入前提前生成GUID并存储到变量中，避免在INSERT语句中直接调用NEWID()，确保计算列使用的是固定值：

-- 预生成需要的GUID
DECLARE @insertGuid UNIQUEIDENTIFIER = NEWID()
DECLARE @updateGuid UNIQUEIDENTIFIER = NEWID()

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp 
(
    ExternalId UNIQUEIDENTIFIER NULL,
    UniqueId UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID()),
    PersistedId AS ISNULL(ExternalId, UniqueId) PERSISTED
)

-- 使用预生成的GUID插入
INSERT INTO #tmp (ExternalId) 
VALUES (null), (@insertGuid)
SELECT * FROM #tmp

-- 使用预生成的GUID更新
UPDATE #tmp
SET externalid = CASE WHEN ExternalId IS NULL THEN @updateGuid ELSE null END
SELECT * FROM #tmp

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