Converting an Existing SQL Server Table to a Temporal Table

One of the very useful features added in SQL Server 2016 were temporal tables.  With a temporal table, SQL Server will automatically record a history of all changed data rows to a history table associated with the temporal table.  Further, SQL Server gives us some new syntax to be able to easily query what the data in the table looked like at any point in time or to show the entire history of a row in a table.  If you want more details, you can check out this earlier blog post I wrote on temporal tables.

However, what if we have an existing table in our database that we want to convert to a temporal table?  Lets take a look at how we do that.

For this example, lets assume that we have the following table that already exists in our database.

1 2 3 4 5 6 7 8 9 10

CREATE TABLE Employees ( EmployeeId INT NOT NULL, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Email VARCHAR(50) NOT NULL, Phone VARCHAR(20) NULL, CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId) );

To convert this table to a temporal table, it is a two step process.  The first step is that we need to add our ValidFrom/ValidTo columns to the table to represent when the row was active in the table.  So we can run the following statement to do this.

1 2 3 4 5 6

ALTER TABLE Employees ADD ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT '1900-01-01 00:00:00.000', ValidTo DATETIME2(3) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT '9999-12-31 23:59:59.999', PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

Here we are adding the two columns needed for when the row is valid and the PERIOD that is required by a temporal table.  Some things to note:

• We could name the columns ValidFrom and ValidTo anything that we want to, these are just the names that I chose.
• These columns must be of a DATETIME2 data type.  In this case, I am using DATETIME2(3) to go down to millisecond precision.
• We need to provide default values for these columns in order to populate the existing rows on the table.  For my ValidFrom I chose 1/1/1900 as a default starting date.  The ending date for the rows in ValidTo column must be the maximum date/time value for our data type, so in this case, 12/31/999 at 23:59:99.999.
• Otherwise, the syntax for the columns look much like the syntax for the columns in the CREATE TABLE statement.

Then, we need to run step 2 of the process:

1 2	ALTER TABLE Employees SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));

Here, we turn on SYSTEM_VERSIONING for the table so the ValidFrom and ValidTo dates will be auto-generated and define the name of the history table to use.

And that is all there is to it.  Now, your table has been converted to a temporal table and any changes to your table will be tracked in the history table.