About SQL Server Error 13542
Recently we have received a problem report about the following error: Msg 13542, Level 16, State 0, Line 42 ADD PERIOD FOR SYSTEM_TIME on table 'xxx' failed because there are open records with start of period set to a value in the future. I would like to share the cause of the problem and the solution here to help DBAs experiencing the same problem. Background: You can add versioning data to an existing non-temporal table with data populated to make it a temporal table. The document below contains a sample script in the "Add versioning to non-temporal tables" section for this purpose. Create a system-versioned temporal table - SQL Server | Microsoft Learn Note that in the sample script, the column for "ROW START" column the data type is set to DATETIME2 as follows. ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME() If you change DATETIME2 to DATETIME2(x), by specifying a precision x (range is 0 - 7) for the floating-point part, the aforementioned error may occur when adding the value of the ValidFrom column for existing data. Cause: When adding the value of SYSUTCDATETIME() as the default ValidFrom value for existing data, because of the precision specified with the DATATIME2(x), SQL Server needs to round the time to the nearest value with that precision. This can be either "round up" or "round down", depending whether the last digit within the precision is larger than 4 or not. In the case of "round up", the result is a time value in the future. Workaround: Change DEFAULT SYSUTCDATETIME() to DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME()) so that the time is not in the future even in the case of "round up". ValidFrom DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME()) Documentation for DATETIME2: datetime2 (Transact-SQL) - SQL Server | Microsoft Learn