如何修复时态表历史删除？

Question

对于我的项目，我需要与历史表进行交互，并删除其中的重复行：

Id  Name   SysStartTime SysEndTime
-----------------------------------
123 FooBar 2015-11-15   2015-11-16
123 FooBar 2015-11-16   2015-11-17
123 FooBar 2015-11-17   2015-11-18

但是，很明显，任何删除操作都会破坏历史记录间隔SysStartTime、SysEndTime

ALTER TABLE [dbo].[MyTable] 
    SET (SYSTEM_VERSIONING = OFF)

DELETE FROM [dbo].[MyTableHistory] 
WHERE SysStartTime = '2020-11-16'

ALTER TABLE [dbo].[MyTable] 
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTableHistory]))

和gap将会出现：

123 FooBar 2015-11-15 2015-11-16
--> deleted, but gap between 2015-11-16 and 2015-11-17 is not imprinted in any of those upper/lower revisions
123 FooBar 2015-11-17 2015-11-18

因此，这将影响您需要在此日期查找实体的所有历史查询。

有没有什么内置的方法，而不是编写一些丑陋的SQL来解决这个问题？最好，它应该是以前的一个版本，但任何一个都可以。

Answer 1

如果我理解正确的话，您希望从历史表中删除一行(或多行)，并更改前面的行以将其隐藏起来-有效地从历史中删除更改。下面是一个T-SQL脚本，它将为您完成此操作-删除特定日期以来的所有更改。

DECLARE @DeletedDates AS TABLE
(
    DateFrom DateTime2(7),
    DateTo DateTime2(7)
);

BEGIN TRANSACTION; -- I'm assuming this is an atomic operation...
BEGIN TRY

ALTER TABLE [dbo].[MyTable] SET ( SYSTEM_VERSIONING = OFF )

DELETE 
FROM [dbo].[MyTableHistory] 
OUTPUT deleted.SysStartTime, deleted.SysEndTime INTO @DeletedDates
WHERE SysStartTime >= '2020-11-16T00:00:00' 
AND SysStartTime < '2020-11-17T00:00:00'
-- Note: SysStartTime and SysEndTime are datetime2(7). 
-- I'm assuming you want to delete changes in a specific date.

UPDATE t
SET SysEndTime = DateTo
FROM [dbo].[MyTableHistory] AS t
JOIN @DeletedDates AS DeletedDates
ON t.SysEndTime = DeletedDates.DateFrom;

ALTER TABLE [dbo].[MyTable] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTableHistory]));

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW; 
END CATCH