Temporal Tables in SQL Server

A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

The above definition taken from MS docs link. It has all the necessary information about how it works, how to query it, its limitation and consideration.

Making a table Temporal

  1. Lets say you have a table named Employee having below data:
Table Contents

2. Now to maintain the historical data for all the changes as per the Temporal table we need to do the following below steps :

a. Add Begin and End Timestamp columns.
b. Add Default Constraints to above column to have system time in Begin_timestamp and 9999.12.31 23:59:59.9999999 as End_timestamp.
c. Add the Period for System_Time.
d. Enable System_Versioning and provide the history table name.

ALTER TABLE [dbo].[Employee] ADD Version_Begin_Timestamp DATETIME2 NOT NULL CONSTRAINT Version_Begin_Timestamp Default SYSUTCDATETIME(); 
GO

ALTER TABLE [dbo].[Employee] ADD Version_End_Timestamp DATETIME2 NOT NULL CONSTRAINT Version_End_Timestamp DEFAULT '9999.12.31 23:59:59.9999999'
GO

ALTER TABLE [dbo].[Employee] ADD PERIOD FOR SYSTEM_TIME (Version_Begin_Timestamp, Version_End_Timestamp); 
GO

ALTER TABLE [dbo].[Employee] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employee_History))
GO

Once above is done you will be able to see the table and history table attached with it as below:

Temporal Enabled

Removing Temporal

To remove the Temporal follow the below steps,
1. Set system_versioning off for the table.
2. Drop the Period created on the table.
3. Drop the constraints which maintain the timestamps.
4. Drop the columns created for maintaining the timestamps of modification or changes into the table.

ALTER TABLE [dbo].[Employee]  SET (SYSTEM_VERSIONING = OFF)
GO

ALTER TABLE [dbo].[Employee]  DROP PERIOD FOR SYSTEM_TIME;
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [Version_Begin_Timestamp]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [Version_End_Timestamp]
GO

ALTER TABLE [dbo].[Employee]  DROP COLUMN [Version_Begin_Timestamp]
GO

ALTER TABLE [dbo].[Employee]  DROP COLUMN [Version_End_Timestamp]
GO

Once you run the above script you will be able to see Base table and history table link broken and history table is visible as a normal table.

History and Base table

Why the Constraints?

There are two constraints that we need to create for Temporal to work:

  1. Constraint to add the current system date time into the begin time stamp of the row.
  2. Another constraint to add 9999.12.31 23:59:59.9999999 as the end date.

Whenever any change/insert is made to the row the Begin_timestamp gets updated with the date and time the modification was made and historical time is maintained using these two (begin and end_timestamp) columns.

What if we need to de-attach and re-attach the history table

Now if due to some reason we need to remove the link between the two, base and history table and have to add it back. That can easily be done using the below commands:

--First Remove System Versioning
ALTER TABLE [dbo].[Employee]  SET (SYSTEM_VERSIONING = OFF)
GO

ALTER TABLE [dbo].[Employee]  DROP PERIOD FOR SYSTEM_TIME;
GO
--
-- To reattach the table back use below command
ALTER TABLE [dbo].[Employee] ADD PERIOD FOR SYSTEM_TIME (Version_Begin_Timestamp, Version_End_Timestamp); 
GO

ALTER TABLE [dbo].[Employee] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employee_History))
GO

The History and Base tables can be again linked together but we always need to consider that there will be some data defect in this Temporal table as explained below with an example.

I have made the above change and can see that history now has the older values.

I will remove the System versioning and make another modification to the table and add it back.

Removing + Changing + Re-attaching the history
Missing record

Post adding the history table back, you can see we have been able to attach the history back but the entry of LastName having value “Patriot” is missing from the table.

Another thing is if we try to query the Temporal table using AS ON command with any time in between the End_timestamp of history record and Begin_timestamp of the base table and row.

Timestamps

We wont be getting any state for the Emp_id 1 as the time chain is broken.

Data defect

I hope it helps!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s