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

Question

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

所以。我只是使用这个工具来创建表模式，然后使用SSIS来移动数据。

因为我现有的历史表中有数据，所以我希望保留SysStartDate和SysEndDate字段。为了做到这一点，我不得不在Azure数据库中禁用SYSTEM_VERSIONING，并将句点删除到表中。

数据迁移是成功的，所以我在表上重新创建了我的句点，但是当我试图使用指定的历史表启用SYSTEM_VERSIONING时，我得到以下错误：

Msg 13573，16级，状态0，第34行 将SYSTEM_VERSIONING设置为ON失败，因为历史表“xxxxxHistory” 包含重叠记录。

我觉得这很奇怪，因为现有的表最初是以时态表的形式加入的，所以我不明白为什么现在会发生冲突。

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，16级，状态0，第34行 将SYSTEM_VERSIONING设置为ON失败，因为历史表“xxxxxHistory” 包含重叠记录。

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

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

我找到了这个错误的解释：

“同一记录有多个记录，具有重叠的开始日期和结束日期。历史表中最后一行的结束日期应该与父表中活动记录的开始日期相匹配”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，有多行具有完全相同的开始结束日期和结束日期。Server似乎认为它们只是空间中的一个点，而不是间隔。只有当您请求它们存在的精确毫秒时，它们才会出现。
• 历史记录行的结束日期与下一个记录行的开始日期之间存在差距。SQL server认为PK只是在那个时间间隔内不存在。

Answer 2

时态表依赖于时态表的主键值，结合SysStartTime确定历史表的唯一性。

如果对主键定义进行更改，则很容易发生这种情况。另外，如果没有填充与时态表的PK对应的历史表的字段，或者使用默认值填充了许多/所有字段，则会检测到重叠并得到该错误。

检查您的PK是否在系统版本化的时态表上定义，然后检查历史表的主键字段中相应的值是否正确(即对于任何给定的PK & SysStartTime值都是唯一的)。

在再次应用系统版本关系之前，您可能必须相应地更新历史表。