SQL with Manoj

In my [previous post] about Temporal Data I showed how to setup Temporal on an existing table. That was a simple scenario where the tables does not have historical data, and we added the 2 Period columns on that table, and we didn’t face any issues.
 

–> Check demo to setup Temporal Tables:

–> Resolving potential issues while enabling Temporal Data on an existing Table:

There might be other scenarios where an existing table already contains the Period columns and you want to assign them as the Temporal Period Columns. Here, in this post I’ve tried to do the same with an existing table and ran into some issues while setting it up. Resolved the issues in process to make that table a Temporal Table, which we will see below:
 

–> My existing table looks like this, EffectiveStartDate & EffectiveEndDate datetime type columns as Period Columns:

CREATE TABLE dbo.ResourceTD (
	 ResourceID
	 -- other columns here
	,EffectiveStartDate datetime,
	,EffectiveEndDate datetime,
)

–> 1. First Issue: While setting up Parent Temporal Table with the ALTER TABLE statement:

ALTER TABLE dbo.ResourceTD 
ADD PERIOD FOR SYSTEM_TIME (
    EffectiveStartDate, 
    EffectiveEndDate
)
GO

… I got following error below:

Msg 13575, Level 16, State 0, Line 42
ADD PERIOD FOR SYSTEM_TIME failed because table ‘SQL2016POC.dbo.ResourceTD’ contains records where end of period is not equal to MAX datetime.

This was evident by the error message, as the columns datatype was datetime initially and I ALTERed them to datetime2 but the values set were not MAX, but like this “9999-12-31 00:00:00”. So, you need to set them to the MAX possible date, like “9999-12-31 23:59:59.9999999” as shown below:

UPDATE dbo.ResourceTD 
SET EffectiveEndDate = '9999-12-31 23:59:59.9999999'
GO

–> 2. Second Issue: Finally, while enabling the Temporal Table with History Table:

-- Enabling the System-Versioning ON
ALTER TABLE dbo.ResourceTD 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ResourceTDHistory))
GO

… I got following error below:

Msg 13541, Level 16, State 0, Line 85
Setting SYSTEM_VERSIONING to ON failed because history table ‘SQL2016POC.dbo.ResourceTDHistory’ contains invalid records with end of period set before start.

I found that the EffectiveStartDate & EffectiveEndDate columns values were not correct, actually EffectiveStartDate was greater than EffectiveEndDate. That might be due to some previous processing of business logic and manual error. So, I swapped the value for those records.
 

–> You will need to check for all possible scenarios until the Historical Table gets created successfully, some as follows (but not limited to these):

-- Scenario #1
SELECT * FROM ResourceTDHistory WHERE EffectiveStartDate IS NULL
SELECT * FROM ResourceTDHistory WHERE EffectiveEndDate IS NULL

-- Scenario #2
SELECT * 
FROM ResourceTDHistory 
WHERE EffectiveStartDate > EffectiveEndDate

I faced these two issues related to this single table. You might face similar or other issues with other tables, based upon different scenarios.

I will update this post if I see any other issues while setting up Temporal tables. Or if you come across, please let me know by your comments, Thanks !!!
 

Check & Like my FB Page