Temporal Table on existing table

Question

Trying to do this:

ALTER TABLE [testndb].[dbo].[HrEmployees] ADD ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN
constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())
, ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END HIDDEN
constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ALTER TABLE [dbo].[HrEmployees] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HrEmployees_Temporal));

The HrEmployees_Temporal has all the same columns as the HrEmployees table. When I Execute the code to turn on the SYSTEM_VERSIONING, I get this error:

Msg 13574, Level 16, State 1, Line 27 Setting SYSTEM_VERSIONING to ON failed because temporal history table 'testndb.dbo.HrEmployees_Temporal' contains SYSTEM_TIME period.

I have dropped constraints and tried to build them again. I have deleted the Temporal table and recreated making certain I placed the ValidFrom and ValidTo columns:

[ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,

This is the CONSTRAINT

CONSTRAINT [mtpk_cs560431_Temporal] PRIMARY KEY CLUSTERED ( [SourceID] ASC, [EmployeeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]) ) ON [PRIMARY] GO

Why would it fail on "SYSTEM_TIME" when I "think" that is correctly formed?

Answer 1

well, after a a short time I figured it out. It was in my history table. I had used my primary table script to build the Historic table. This included this line of code:

CONSTRAINT [pk_EmpTemp] PRIMARY KEY CLUSTERED ( [SourceID] ASC, [EmployeeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]) ) ON [PRIMARY]

The Bolded section is where the problem was. I removed the constraints and was able to create the History table.