sql-server - 启用 SYSTEM_VERSIONING 错误 - 历史表中的重叠日期

Question

我最近将我的 SQL 2019 数据库从虚拟机迁移到了 Azure SQL。我使用了 MS 数据迁移工具，但不幸的是，它不会从临时表迁移数据。

所以。我只是使用该工具创建表架构，然后使用 SSIS 移动数据。

因为我现有的历史表中有数据，所以我想保留 SysStartDate 和 SysEndDate 字段。为此，我必须在我的 Azure SQL 数据库中禁用 SYSTEM_VERSIONING 并在表上删除 PERIOD。

数据迁移成功，所以我在表上重新创建了 PERIOD，但是当我尝试使用指定的历史表启用 SYSTEM_VERSIONING 时，出现以下错误:

Msg 13573, Level 16, State 0, Line 34
Setting SYSTEM_VERSIONING to ON failed because history table 'xxxxxHistory'
contains overlapping records.

我觉得这很奇怪，因为现有表最初是作为时态表连接的，所以我不明白为什么现在会有冲突。

ALTER TABLE xxx.xxx 
ADD PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)    

ALTER TABLE xxx.xxx 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=xxx.xxxHistory))

我希望得到一个成功的时态表。相反，我收到以下错误:

Msg 13573, Level 16, State 0, Line 34
Setting SYSTEM_VERSIONING to ON failed because history table 'xxxxxHistory'
contains overlapping records.

我运行了以下查询来识别重叠，但我没有得到任何重叠:

SELECT 
     xxxxKeyNumeric
     ,SysStartTime
     ,SysEndTime
FROM 
    xxxx.xxxxhistory o
WHERE EXISTS
(
    SELECT 
        1 
    FROM 
        xxxx.xxxxhistory o2
    WHERE 
        o2.xxxxKeyNumeric = o.xxxxKeyNumeric
        AND o2.SysStartTime <= o.SysEndTime
        AND o.SysStartTime <= o2.SysEndTime
        AND o2.xxxxPK != o.xxxxPK
)
ORDER BY 
    o.xxxxKeyNumeric, 
    o.SysStartTime

Answer 1

我找到了错误的解释:

“同一条记录有多个记录，开始日期和结束日期重叠。历史表中最后一行的结束日期应与父表中事件记录的开始日期相匹配”blog of a DBA

这发生在我切换历史表后，触摸了几行，然后试图回到旧的历史表。

更新:再次发生，这次表有数百万行。我不得不编写一个查询，比较历史表中每一行的开始日期和结束日期。

可能的原因:

1. 对于每个 PK，历史记录行的开始日期和结束日期不得重叠。下面的查询将找到这个特定问题。
2. 该 PK 历史记录中最新行的结束日期晚于主表中 PK 的开始日期。可以修改上述查询来执行此操作
3. 在具有相同 PK 的行中，2 行覆盖相同的时间间隔。如果它们重叠一毫秒，​​而有人请求精确的毫秒数，它就不知道这两个版本中哪个是正确的。

第一期:

select ant.*,post.* , DATEDIFF(day,ant.end_date,post.start_date)
from
(SELECT
      PK_column
    , start_date
    , end_date
    , ROW_NUMBER() OVER(PARTITION BY PK_column ORDER BY end_date desc, start_date desc)    AS current
    ,(ROW_NUMBER() OVER(PARTITION BY PK_column ORDER BY end_date desc, start_date desc))-1 AS previous
FROM huge_table_HIST 
) ant
inner join
(SELECT
      key_column
    , start_date
    , end_date
    , ROW_NUMBER() OVER(PARTITION BY PK_column ORDER BY end_date desc, start_date desc ) AS current
    FROM huge_table_HIST 
) post
ON ant.PK_column=post.PK_column AND ant.previous=post.current
WHERE ant.end_date > post.start_date

令人惊讶的是，如果出现以下情况，它不会失败:

• 对于相同的 PK，您有多个具有完全相同的开始结束日期和结束日期的行。 SQL Server 似乎将它们视为空间中的一个点，而不是一个区间。只有当您请求它们存在的精确毫秒时，它们才会出现。
• 历史记录行的结束日期与下一行的开始日期之间存在间隔。 SQL 服务器认为 PK 在该时间间隔内不存在。