如何避免TSQL时态表中生成重复历史条目？

问题描述 #

我们的SQL Server数据库中，主表关联了时态表。当用户执行更新操作但未修改实际数据时，时态表仍会生成新的历史条目，导致出现多条内容完全一致、仅时间戳不同的重复记录。尝试给时态表添加唯一索引时，系统返回错误：

Cannot create UNIQUE index on temporal history table

主表已存在唯一索引阻止重复行，问题核心并非主表数据重复，而是时态表的无意义重复历史行。

表结构如下：

CREATE TABLE controls.slot_table
(
    -- 主键
    ID_auto INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

    -- 业务列
    control_source_ID           INT,
    index_in_PLC                VARCHAR(1700),
    part_ID                     INT,
    panel_rack_slot_combo_AF    VARCHAR(1700),
    card_type_AF                VARCHAR(1700),
    IO_points_used_AF           INT,
    max_points_AF               INT,
    module_number               INT,
    slot_comment                VARCHAR(1700),
    lookup_location_sector_AF   VARCHAR(1700),
    lookup_location_room_AF     VARCHAR(1700),
    calc_slot_percent_spare_AF  DECIMAL(20,10),

    -- 约束与外键
    CONSTRAINT FK_slot_table_control_source_ID  
        FOREIGN KEY (control_source_ID) REFERENCES controls.source_standard(ID_auto),
    CONSTRAINT FK_slot_table_part_ID            
        FOREIGN KEY (part_ID) REFERENCES part.part_catalog(ID_auto),

    -- 系统版本控制所需附加列
    modified_by     VARCHAR(1700),
    modified_date   DATETIME2,
    created_by      VARCHAR(1700),
    created_date    DATETIME2,
    date_start  DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    date_end    DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME(date_start, date_end)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = controls.slot_table_history));
GO

-- 主表上的唯一过滤索引
DROP INDEX IF EXISTS DUPE_FILTER_Slot_Combo ON controls.slot_table

CREATE UNIQUE INDEX DUPE_FILTER_Slot_Combo
ON controls.slot_table (control_source_ID, index_in_PLC)
WHERE (control_source_ID IS NOT NULL) AND (index_in_PLC IS NOT NULL)

解决方案 #

方法1：更新语句中添加条件判断 #

直接在UPDATE语句中加入逻辑，仅当实际业务数据发生变化时才执行更新操作：

UPDATE controls.slot_table
SET 
    control_source_ID = @new_control_source_ID,
    index_in_PLC = @new_index_in_PLC,
    part_ID = @new_part_ID,
    panel_rack_slot_combo_AF = @new_panel_rack_slot_combo_AF,
    card_type_AF = @new_card_type_AF,
    IO_points_used_AF = @new_IO_points_used_AF,
    max_points_AF = @new_max_points_AF,
    module_number = @new_module_number,
    slot_comment = @new_slot_comment,
    lookup_location_sector_AF = @new_lookup_location_sector_AF,
    lookup_location_room_AF = @new_lookup_location_room_AF,
    calc_slot_percent_spare_AF = @new_calc_slot_percent_spare_AF,
    modified_by = @current_user,
    modified_date = GETUTCDATE()
WHERE 
    ID_auto = @target_id
    AND (
        -- 逐一对比业务列，处理NULL值判断
        control_source_ID != @new_control_source_ID OR (control_source_ID IS NULL AND @new_control_source_ID IS NOT NULL) OR (control_source_ID IS NOT NULL AND @new_control_source_ID IS NULL)
        OR index_in_PLC != @new_index_in_PLC OR (index_in_PLC IS NULL AND @new_index_in_PLC IS NOT NULL) OR (index_in_PLC IS NOT NULL AND @new_index_in_PLC IS NULL)
        OR ISNULL(part_ID, -1) != ISNULL(@new_part_ID, -1)
        OR panel_rack_slot_combo_AF != @new_panel_rack_slot_combo_AF
        OR card_type_AF != @new_card_type_AF
        OR ISNULL(IO_points_used_AF, -1) != ISNULL(@new_IO_points_used_AF, -1)
        OR ISNULL(max_points_AF, -1) != ISNULL(@new_max_points_AF, -1)
        OR ISNULL(module_number, -1) != ISNULL(@new_module_number, -1)
        OR slot_comment != @new_slot_comment
        OR lookup_location_sector_AF != @new_lookup_location_sector_AF
        OR lookup_location_room_AF != @new_lookup_location_room_AF
        OR calc_slot_percent_spare_AF != @new_calc_slot_percent_spare_AF
        -- 若需记录修改人/时间的变更，添加以下判断
        OR modified_by != @current_user
        OR modified_date != GETUTCDATE()
    )

说明：对于可空列，需特殊处理NULL值的比较（因为NULL != NULL结果为UNKNOWN，不会触发更新），可通过ISNULL指定一个业务中不存在的默认值，或显式判断NULL状态的变化。

方法2：创建INSTEAD OF UPDATE触发器 #

如果无法修改所有更新语句，可通过触发器拦截无意义的更新操作：

CREATE TRIGGER trg_slot_table_prevent_empty_update
ON controls.slot_table
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅当业务列或指定系统列发生变化时执行更新
    UPDATE t
    SET 
        control_source_ID = i.control_source_ID,
        index_in_PLC = i.index_in_PLC,
        part_ID = i.part_ID,
        panel_rack_slot_combo_AF = i.panel_rack_slot_combo_AF,
        card_type_AF = i.card_type_AF,
        IO_points_used_AF = i.IO_points_used_AF,
        max_points_AF = i.max_points_AF,
        module_number = i.module_number,
        slot_comment = i.slot_comment,
        lookup_location_sector_AF = i.lookup_location_sector_AF,
        lookup_location_room_AF = i.lookup_location_room_AF,
        calc_slot_percent_spare_AF = i.calc_slot_percent_spare_AF,
        modified_by = i.modified_by,
        modified_date = i.modified_date
    FROM controls.slot_table t
    INNER JOIN inserted i ON t.ID_auto = i.ID_auto
    INNER JOIN deleted d ON t.ID_auto = d.ID_auto
    WHERE 
        -- 逐一对比所有需监控的列，处理NULL值
        t.control_source_ID != i.control_source_ID OR (t.control_source_ID IS NULL AND i.control_source_ID IS NOT NULL) OR (t.control_source_ID IS NOT NULL AND i.control_source_ID IS NULL)
        OR t.index_in_PLC != i.index_in_PLC OR (t.index_in_PLC IS NULL AND i.index_in_PLC IS NOT NULL) OR (t.index_in_PLC IS NOT NULL AND i.index_in_PLC IS NULL)
        OR t.part_ID != i.part_ID OR (t.part_ID IS NULL AND i.part_ID IS NOT NULL) OR (t.part_ID IS NOT NULL AND i.part_ID IS NULL)
        OR t.panel_rack_slot_combo_AF != i.panel_rack_slot_combo_AF OR (t.panel_rack_slot_combo_AF IS NULL AND i.panel_rack_slot_combo_AF IS NOT NULL) OR (t.panel_rack_slot_combo_AF IS NOT NULL AND i.panel_rack_slot_combo_AF IS NULL)
        OR t.card_type_AF != i.card_type_AF OR (t.card_type_AF IS NULL AND i.card_type_AF IS NOT NULL) OR (t.card_type_AF IS NOT NULL AND i.card_type_AF IS NULL)
        OR t.IO_points_used_AF != i.IO_points_used_AF OR (t.IO_points_used_AF IS NULL AND i.IO_points_used_AF IS NOT NULL) OR (t.IO_points_used_AF IS NOT NULL AND i.IO_points_used_AF IS NULL)
        OR t.max_points_AF != i.max_points_AF OR (t.max_points_AF IS NULL AND i.max_points_AF IS NOT NULL) OR (t.max_points_AF IS NOT NULL AND i.max_points_AF IS NULL)
        OR t.module_number != i.module_number OR (t.module_number IS NULL AND i.module_number IS NOT NULL) OR (t.module_number IS NOT NULL AND i.module_number IS NULL)
        OR t.slot_comment != i.slot_comment OR (t.slot_comment IS NULL AND i.slot_comment IS NOT NULL) OR (t.slot_comment IS NOT NULL AND i.slot_comment IS NULL)
        OR t.lookup_location_sector_AF != i.lookup_location_sector_AF OR (t.lookup_location_sector_AF IS NULL AND i.lookup_location_sector_AF IS NOT NULL) OR (t.lookup_location_sector_AF IS NOT NULL AND i.lookup_location_sector_AF IS NULL)
        OR t.lookup_location_room_AF != i.lookup_location_room_AF OR (t.lookup_location_room_AF IS NULL AND i.lookup_location_room_AF IS NOT NULL) OR (t.lookup_location_room_AF IS NOT NULL AND i.lookup_location_room_AF IS NULL)
        OR t.calc_slot_percent_spare_AF != i.calc_slot_percent_spare_AF OR (t.calc_slot_percent_spare_AF IS NULL AND i.calc_slot_percent_spare_AF IS NOT NULL) OR (t.calc_slot_percent_spare_AF IS NOT NULL AND i.calc_slot_percent_spare_AF IS NULL)
        OR t.modified_by != i.modified_by
        OR t.modified_date != i.modified_date;
END
GO

方法3：清理已存在的重复历史记录（可选） #

如果时态表中已经存在大量重复历史记录，可先关闭系统版本控制再清理：

-- 关闭系统版本控制
ALTER TABLE controls.slot_table SET (SYSTEM_VERSIONING = OFF);

-- 删除重复记录，保留每个业务数据组合的最新历史条目
WITH ranked_history AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY 
                ID_auto,
                control_source_ID,
                index_in_PLC,
                part_ID,
                panel_rack_slot_combo_AF,
                card_type_AF,
                IO_points_used_AF,
                max_points_AF,
                module_number,
                slot_comment,
                lookup_location_sector_AF,
                lookup_location_room_AF,
                calc_slot_percent_spare_AF,
                modified_by,
                modified_date,
                created_by,
                created_date
            ORDER BY date_start DESC
        ) AS rn
    FROM controls.slot_table_history
)
DELETE FROM ranked_history WHERE rn > 1;

-- 重新开启系统版本控制
ALTER TABLE controls.slot_table SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = controls.slot_table_history));
GO

注意：执行此操作前务必备份数据，避免误删重要历史记录。

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