Altering existing tables with data to system-versioned temporal table

If you have created a database in SQL Server prior to 2016, you might have created a history (or version or log) table to maintain the history of sensitive data. You might have created triggers for inserting data to the history table whenever any insert, update or delete operation happens on the main table. After migrating your database to a version 2016 or later, you might need to consider migrating this classic tables to built-in temporal system. Altering existing tables to system-versioned temporal table along with data is fairly simple.

Steps for migrating existing tables to system temporal table

Here are the steps involved in migrating an existing history table to system-versioned temporal table:

1. Drop the triggers used for versioning in classic tables.
2. Alter the current (main) table and add a pair of datetime2 columns one for validity start time and another for validity end time. (NOTE: if these columns exist, make sure they are not-nullable)
3. Alter the history table add a pair of datetime2 columns. (NOTE: if these columns exist, make sure they are not-nullable)
4. Alter the current table and add validity start and end column in PERIOD FOR SYSTEM_TIME.
5. Alter the current table and switch on SYSTEM_VERSIONING, specify history table in HISTORY_TABLE parameter and switch on DATA_CONSISTENCY_CHECK.

Here is the sample script. For this example, consider that the classic current / main table is People, history / version table is PeopleHistory and the trigger name is People_OnUpdateDelete.

Before executing the script

Script for migrating to a system temporal table

/* Drop trigger */
DROP TRIGGER People_OnUpdateDelete;
 
/* Alter the current table to make the validity start and end time columns as datetime2 and not nullable */
ALTER TABLE People ALTER COLUMN [PeriodStartTime] datetime2 NOT NULL;
ALTER TABLE People ALTER COLUMN [PeriodEndTime] datetime2 NOT NULL;
 
/* Alter the history table to make the validity start and end time columns as datetime2 and not nullable */
ALTER TABLE PeopleHistory ALTER COLUMN [PeriodStartTime] datetime2 NOT NULL;
ALTER TABLE PeopleHistory ALTER COLUMN [PeriodEndTime] datetime2 NOT NULL;
 
/* In current table add PERIOD FOR SYSTEM_TIME parameter */
ALTER TABLE People
    ADD PERIOD FOR SYSTEM_TIME ([PeriodStartTime], [PeriodEndTime])
 
/* In current table add parameters SYSTEM_VERSIONING, HISTORY_TABLE and DATA_CONSISTENCY_CHECK */
ALTER TABLE People
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PeopleHistory, DATA_CONSISTENCY_CHECK = ON));

After executing the script

Reference

• About system-versioned temporal tables.
• More about temporal tables at Microsoft Docs.