内存优化表中timestamp列的替代方案咨询

内存优化表不支持timestamp（或rowversion）数据类型，针对你用该列检查行是否被修改的需求，以下是几种可行的替代方案：

方案1：使用bigint类型维护自增版本号 #

这是最贴近原timestamp逻辑的替代方式，通过维护一个自增的版本号来追踪行修改：

• 创建bigint类型的版本列，默认值设为0
• 借助原生编译触发器（内存优化表支持）自动更新版本号，避免应用端手动维护的繁琐

示例代码 #

-- 创建内存优化表
CREATE TABLE dbo.MemoryOptimizedTable
(
    ID INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    Data NVARCHAR(100),
    RowVersion BIGINT NOT NULL DEFAULT 0
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- 创建原生编译触发器，更新行时自动递增版本号
CREATE TRIGGER dbo.trg_MemoryOptimizedTable_UpdateVersion
ON dbo.MemoryOptimizedTable
WITH NATIVE_COMPILATION, SCHEMABINDING
AFTER UPDATE
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    UPDATE dbo.MemoryOptimizedTable
    SET RowVersion = RowVersion + 1
    FROM inserted
    WHERE MemoryOptimizedTable.ID = inserted.ID;
END;

应用端逻辑：读取行时记录RowVersion的值，更新时将该值作为条件之一，若更新行数为0则说明行已被其他进程修改。

方案2：用datetime2记录最后修改时间 #

通过记录行的最后修改时间来判断是否发生变更，同时还能保留修改时间的溯源信息：

• 创建datetime2(7)类型的列（精度到100纳秒，高并发下重复概率极低），默认值设为当前UTC时间
• 同样通过触发器自动更新该时间戳

示例代码 #

CREATE TABLE dbo.MemoryOptimizedTable
(
    ID INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    Data NVARCHAR(100),
    LastModified DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME()
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

CREATE TRIGGER dbo.trg_MemoryOptimizedTable_UpdateLastModified
ON dbo.MemoryOptimizedTable
WITH NATIVE_COMPILATION, SCHEMABINDING
AFTER UPDATE
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    UPDATE dbo.MemoryOptimizedTable
    SET LastModified = SYSUTCDATETIME()
    FROM inserted
    WHERE MemoryOptimizedTable.ID = inserted.ID;
END;

应用端逻辑：读取行时记录LastModified的值，更新时对比该值，若不匹配则说明行已被修改。

方案3：利用内存优化表的内置行版本控制 #

如果你的数据库启用了快照隔离或读提交快照隔离，内存优化表会自动维护行版本，无需额外添加列：

• 读取数据时使用WITH (SNAPSHOT)提示获取当前版本的行数据
• 更新时带上读取到的所有列值作为条件，若更新行数为0则说明行已被其他进程修改

示例代码 #

-- 读取目标行的快照版本
SELECT ID, Data FROM dbo.MemoryOptimizedTable WITH (SNAPSHOT) WHERE ID = 1;

-- 尝试更新，带上读取到的原始Data值作为条件
UPDATE dbo.MemoryOptimizedTable
SET Data = 'UpdatedValue'
WHERE ID = 1 AND Data = 'OriginalValue';

-- 检查更新行数，若为0则表示行已被修改
IF @@ROWCOUNT = 0
BEGIN
    -- 处理行已被修改的逻辑
END;

这种方式适合列较少的表，列过多时条件会变得冗长。

方案对比 #

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