Msg 13575 OR Msg 13542 error message while adding period columns
You may see following error message while trying to enable system-versioned temporal table and adding period columns for a table which contain data:
1st error message:
Msg 13575, Level 16, State 0, Line 32
ADD PERIOD FOR SYSTEM_TIME failed because table 'temporaltest.dbo.CUSTOMERINFO' contains records where end of period is not equal to MAX datetime.
2nd error message:
Msg 13542, Level 16, State 0, Line 8733
ADD PERIOD FOR SYSTEM_TIME on table ' temporaltest.dbo.CUSTOMERINFO' failed because there are open records with start of period set to a value in the future.
This issue can occur for memory optimized table as well as non-memory optimized table.
You can reproduce this issue using following T-SQL:
/************************************************************************************************************************/
CREATE DATABASE [temporaltest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'temporaltest', FILENAME = N'E:\temp\temporaltest.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
FILEGROUP [imoltp_mod] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N'imoltp_mod1', FILENAME = N'e:\temp\imoltp_mod1' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'temporaltest_log', FILENAME = N'E:\temp\temporaltest_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
use temporaltest
GO
CREATE TABLE [dbo].[CUSTOMERINFO]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[EMPID] [int] NOT NULL,
CONSTRAINT [CUSTOMERINFO_primaryKey] PRIMARY KEY NONCLUSTERED HASH
(
[EMPID]
)WITH ( BUCKET_COUNT = 128)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
insert into [CUSTOMERINFO] ([EMPID]) select 1
GO
select * from [CUSTOMERINFO]
GO
ALTER TABLE [CUSTOMERINFO]
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT GETUTCDATE(),
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999');
/************************************************************************************************************************/
WORKAROUND
If you are seeing same symptoms in that case you can break your T-SQL in 4 parts as shown below which should help you to resolve this issue:
/************************************************************************************************************************/
ALTER TABLE [CUSTOMERINFO]
ADD
SysStartTime datetime2 NOT NULL DEFAULT GETUTCDATE(),
SysEndTime datetime2 NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999');
GO
ALTER TABLE [CUSTOMERINFO]
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO
ALTER TABLE [CUSTOMERINFO]
alter column SysStartTime ADD HIDDEN;
GO
ALTER TABLE [CUSTOMERINFO]
alter column SysEndTime ADD HIDDEN;
/************************************************************************************************************************/
Vikas Rana (@vikasrana_dba)
Support Escalation Engineer
Microsoft India GTSC