主页 / dba / 问题 / 200079
Accepted
gotqn
Asked: 2018-03-14 02:05:29 +0800 CST

在表上添加 PERIOD FOR SYSTEM_TIME 失败

  • 772

我有：

  • 包含现有数据的表
  • SQL Server 2016 SP1
  • SQL Server 管理工作室 17.5

我正在使用以下语句使我的表成为临时表：

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT GETUTCDATE()  
   ,[SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2(0), '9999-12-31 23:59:59'),   
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]); 

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]   
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));

问题：

在我的本地 SQL 实例上，我有很多数据库；很奇怪，查询在其中一些上成功运行，而在其中一些上它给了我以下错误：

消息 13542，级别 16，状态 0，第 51 行在表 'dbo.AnalysisCustomRollupsV2JoinGroups' 上添加 PERIOD FOR SYSTEM_TIME 失败，因为有打开的记录的开始时间设置为将来的值。

有时，当我调试/执行查询时，初始查询会成功运行。

我读到，这可能是因为我在表中有现有数据。所以，我改变了这样的逻辑：

  • 创建缓冲区表并用所有记录填充它
  • 从原始表中删除记录
  • 创建临时表
  • 将记录移回并删除缓冲表

同样，在某些数据库上可以，而在其他数据库上则不行。试图解决这个问题，我发现了以下内容：

对于 StartDate，我指定了当前的 UTC 日期——这可以是任何不在未来的日期和时间，但请注意它应该是 UTC 日期和时间。如果我尝试使用 GETDATE，因为我目前在英国夏令时，我会收到以下错误：Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table 'TestAudit.dbo.SomeData'失败，因为有打开的记录将期初设置为将来的值。

以上是什么意思？我需要更改机器时间吗？或者因为我的本地机器不是UTC时间我有时会收到这个错误？

sql-server t-sql

4 个回答

  1. Best Answer

    我想我已经找到了解决问题的方法，但我不会接受这个作为答案，因为我无法解释导致问题的原因并保证这将在任何时候起作用。经过大量测试后找到了修复，如果有人能在这里带来更多的光线，我会很高兴。

    我从来没有datetime2精确地使用过。所以，我回到了这种格式的来源datetime2(0)——Alter Non-Temporal Table to be System-Versioned Temporal Table。与我一直使用的脚本的唯一区别是日期时间函数。我使用GETUTCDATE()过，因为我不需要对datetime(0)（2018-03-15 07:21:02例如）如此精确，在示例中它是 SYSUTCDATETIME(). 所以，我改变了它。

    我创建了一个脚本，如果存在则删除数据库，从备份中恢复数据库，然后循环执行我的代码（正如我所说，我有时会收到错误并且很难重现它）。

    我已经多次运行脚本，但失败的次数不同（有时为 70%，有时为 50%，有时低于）：

    在此处输入图像描述

    我发现这个为什么 GETUTCDATE 早于 SYSDATETIMEOFFSET？讨论新旧日期时间函数之间的差异。然后构建以下查询：

    DECLARE @UTC DATETIME2(0) = GETUTCDATE();
DECLARE @SYSUTC DATETIME2(0) = SYSUTCDATETIME();

WHILE DATEPART(SECOND, @UTC) = DATEPART(SECOND, @SYSUTC)
BEGIN;
    SET @UTC  = GETUTCDATE();
    SET @SYSUTC  = SYSUTCDATETIME();
END;

SELECT @UTC AS [UTC]
      ,@SYSUTC AS [SYS UTC]
      ,DATEPART(SECOND, @UTC) AS [UTC sec]
      ,DATEPART(SECOND, @SYSUTC) AS [SYS UTC sec]
      ,CASE WHEN @UTC < @SYSUTC THEN 1 ELSE 0 END AS [TimeTravelPossible]
      ,CONVERT(DATETIME2(0), @UTC) AS [UTC date]
      ,CONVERT(DATETIME2(0), @SYSUTC) AS [SYS UTC date]
      ,IIF(CONVERT(DATETIME2(0), @UTC) = CONVERT(DATETIME2(0), @SYSUTC), 1, 0) AS [Are The Same];

    我只是想检查是否可以datetime2(0)使用 sys 而不是 sys 日期时间函数获得不同的日期。当然这是可能的。

    在此处输入图像描述

    也许，引擎正在进行的检查正在执行类似的操作，将其当前日期与我的新日期进行比较，这导致了错误 -open records with start of period set to a value in the future.

    我已经像这样更改了脚本并在昨晚执行了 1000 次 - 没有产生错误。所以，我相信我已经解决了这个特定问题，但我不能确定。

    ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT SYSUTCDATETIME()  
   ,[SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),   
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]); 

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]   
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));
    • 7

  2. 这个问题背后的真正原因是因为 SQL Server 不会从datetime/截断不需要的精度datetime2，它会舍入它！

    为了简单地说明这一点，我投射了一个毫秒等于或大于 500 的时间值

    SELECT CAST('10:10:59.563' AS TIME(0))

    结果是：10:11:00，明显大于10:10:59.563

    这同样适用于DATETIME(n)。

    在您的原始示例中，您正在使用GETUTCDATE()，它返回 a DATETIME，其精度为 3（ish）。马上，你会得到一个四舍五入的日期时间，有时会在未来四舍五入。您将遇到从 转换为较低精度的相同问题SYSUTCDATETIME，例如CAST(SYSUTCDATETIME() AS DATETIME2(3))- 如果第 4 个小数位为“5”或更高，则日期将向上舍入到未来。

    为了减轻这种情况，您可以抵消舍入DATEADD以有效地截断不需要的精度，例如

    CAST(DATEADD(MILLISECONDS, -500, SYSUTCDATETIME()) AS DATETIME(0))
CAST(DATEADD(MILLISECONDS, -50, SYSUTCDATETIME()) AS DATETIME(1))
CAST(DATEADD(MILLISECONDS, -5, SYSUTCDATETIME()) AS DATETIME(2))
CAST(DATEADD(MICROSECONDS, -500, SYSUTCDATETIME()) AS DATETIME(3))
CAST(DATEADD(MICROSECONDS, -50, SYSUTCDATETIME()) AS DATETIME(4))
CAST(DATEADD(MICROSECONDS, -5, SYSUTCDATETIME()) AS DATETIME(5))

    这是如何工作的：

    如果我们想要截断到小数点后两位（DP），我们需要在舍入前从该值中减去最小非零值的一半（例如 0.01 / 2 = 0.005）。

    一些例子：

    7.004999999999 - 0.005 = 6.999999999999 round to two DP = 7.00
7.000999999999 - 0.005 = 6.995999999999 round to two DP = 7.00
7.009999999999 - 0.005 = 7.004999999999 round to two DP = 7.00

    DATETIME(0)因此，要编辑您提供的代码，在这种情况下使用的正确方法是：

    ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
  ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN
          CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart 
              DEFAULT CONVERT(DATETIME(0), DATEADD(MILLISECONDS, -500, SYSUTCDATETIME())),
      [SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN
          CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd
              DEFAULT CONVERT(DATETIME2(0), '9999-12-31 23:59:59'),   
      PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]); 

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]   
SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges
));
    • 2

  3. 在一个事务中更新大量表时出现此错误（通过实体框架迁移） 似乎我需要按照参照完整性的顺序更新表，首先执行子表。

    我把这个过程分成几个迁移。

    • 0

  4. 这适用于 SQL Server 和 Azure SQL (PaaS) 环境。

    ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
     ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN 
         CONSTRAINT DF_SysStart DEFAULT DATEADD(second, -1, SYSUTCDATETIME()),
     SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN 
         CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
     PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[AnalysisCustomRollupsV2JoinGroups_History]));

GO
    • -1

相关问题