ADD PERIOD FOR SYSTEM_TIME on table failed

Posted on

Question :

I have:

  • table with existing data
  • SQL Server 2016 SP1
  • SQL Server Management Studio 17.5

I am using the following statement to make a make my table a temporal one:

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));

The issue:

On my local SQL instance I have many databases; it’s very strange that the query runs successfully on some of them, and on some of them it gives me the following error:

Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on
table ‘dbo.AnalysisCustomRollupsV2JoinGroups’ failed because there are
open records with start of period set to a value in the future.

Sometimes, when I am debugging/executing the query, the initial query runs successfully.

I read, that this could be because I have existing data in the table. So, I have change the logic like this:

  • create buffer table and populated it with all records
  • delete the records from the original table
  • create the temporal table
  • move the records back and drop the buffer table

and again, on some databases it is OK, and on other is not. Trying to resolve the issue I have found the following:

For the StartDate I’ve specified the current UTC date – this could be
any date and time that’s not in the future, though note it should be a
UTC one. If I’d tried to use GETDATE, as I’m currently on British
Summer Time, I would get the following error: Msg 13542, Level 16,
State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table
‘TestAudit.dbo.SomeData’ failed because there are open records with
start of period set to a value in the future.

What does the above means? I need to change the machine time? Or because my local machine is not on UTC time I am getting this error sometimes?

Answer :

I think I have found how to fix my issue, but I am not going to accept this as answer as I am not able to explain what is causing the problem and guarantee this will work anytime. It’s fix found after a lot of testing and I will be glad if someone can bring more light here.


I have never used datetime2 with precision. So, I went back to the source of this format datetime2(0)Alter Non-Temporal Table to be System-Versioned Temporal Table. The only difference with the script I have been using was the date time function. I used GETUTCDATE() as I do not need to be so precised with datetime(0)(2018-03-15 07:21:02 for example) and in the example it is SYSUTCDATETIME(). So, I have changed it.

I have create a script which is dropping a database if exists, restoring a database from backup and then executing my code in a loop (as I said I get the error sometimes and it was very hard to reproduce it).

I have run a the script a lot of times and I was getting different number of fails (sometimes 70%, sometimes 50%, sometimes below):

enter image description here

I have found this Why is GETUTCDATE earlier than SYSDATETIMEOFFSET? discussion about differences between old and new date time functions. Then build the following query:

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];

I just wanted to check if I can get different datetime2(0) dates using sys and not sys date time function. And of course it is possible.

enter image description here

Maybe, the checks that the engine is making is doing something like this, comparing its current date with my newer date and this is causing the error – open records with start of period set to a value in the future.

I have change the script like this and executed 1 000 times last night – no errors were generated. So, I believe I have fixed this particular issue, but I can’t be sure.

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));

The real reason behind this issue is because SQL Server doesn’t truncate the unwanted precision from datetime/datetime2, it rounds it!

To show this simply, I cast a time value with milliseconds equal or greater than 500

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

Result is: 10:11:00, which is obviously greater than 10:10:59.563

The same applies to DATETIME(n).

In your original example, you’re taking GETUTCDATE(), which returns a DATETIME, which has a precision of 3(ish).
Right off the bat, you’re getting a rounded datetime which will sometimes be rounded up into the future.
You will experience the same issue casting to a lesser precision from SYSUTCDATETIME,
e.g. CAST(SYSUTCDATETIME() AS DATETIME2(3)) – where the 4th fractional digit is ‘5’ or above, the date is rounded up into the future.

To mitigate this, you can offset the rounding with DATEADD to effectively truncate the unwanted precision,
e.g.

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))

How this works:

If we want to truncate down to two decimal positions (DP), we need to subtract half of the smallest non-zero amount (e.g. 0.01 / 2 = 0.005) from the value before rounding.

Some examples:

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

So to edit your provided code, the proper way to use DATETIME(0) in this situation would be:

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
));

I got this error when updating a lot of tables in one transaction ( via an entity framework migration)
It seemed to turn out that I needed to update the tables in order of referential integrity, doing the child tables first.

I split the process into several migrations.

This worked on SQL Server and Azure SQL (PaaS) environment.

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

Leave a Reply

Your email address will not be published. Required fields are marked *