Convert existing table to temporal table

In the previous post, we have discussed the temporal table, which is introduced in SQL Server 2016 to store the time varying data (current data and the data change history along with the time period). There, we have also learnt how we can create a temporal table from scratch.

In this post “Convert existing table to temporal table“, we are going to learn how we can convert an existing table to a temporal table. For the demo purpose we are using a dummy employee table, created with a very small subset of the data taken from HumanResources.Employee table of adventure works database.

Below is the dummy table which needs to be converted in a temporal table:

We can use below script to create the above sample table with the data for demo purpose:

--Create demo employee master table
CREATE TABLE dbo.EmployeeMaster
(
BusinessEntityID int NOT NULL,
NationalIDNumber nvarchar(15) NOT NULL,
LoginID nvarchar(256) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
BirthDate date NOT NULL,
MaritalStatus nchar(1) NOT NULL,
HireDate date NOT NULL,
Gender nchar(1) NOT NULL
)

--Add sample rows in the table
INSERT INTO dbo.EmployeeMaster(BusinessEntityID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, Gender)
VALUES (1, '295847284', 'adventure-works\ken0', 'Chief Executive Officer', CAST('1969-01-29' AS Date), 'S', CAST('2009-01-14' AS Date), 'M'),
(2, '245797967', 'adventure-works\terri0', 'Vice President of Engineering', CAST('1971-08-01' AS Date), 'S', CAST('2008-01-31' AS Date), 'F'),
(3, '509647174', 'adventure-works\roberto0', 'Engineering Manager', CAST('1974-11-12' AS Date), 'M', CAST('2007-11-11' AS Date), 'M')

Convert existing table to temporal table – Step by step

We are going to discuss how we can convert an existing table into a temporal table, step by step. We need to follow below steps to convert the existing table into a temporal table.

1. Define a primary key on the table
2. Add start time and end time period columns in the table
3. Update row effective from and to period values for existing rows
4. Alter column to add NOT NULL constraint
5. Declare system period columns
6. Enable system versioning on the table

Lets have a detail discussion on each step:

Step 1 – Define a primary key on the table

To convert a table into a temporal table by enabling system versioning on it, we need to define a primary key on the table first. Without defining a primary key, we cannot enable system versioning on the table. Below we are creating a primary key on the employee master table:

ALTER TABLE dbo.EmployeeMaster
ADD CONSTRAINT PK_EmployeeMaster_BusinessEntityID PRIMARY KEY CLUSTERED(BusinessEntityID ASC)

Step 2 – Add start time and end time period columns in the table

Add two columns, StartTime and EndTime, of datetime2 datatype in the table. We can name these columns as per our choice which satisfy the SQL Server identifiers naming conventions. These columns are used to define the effective from and to time period values for the rows in the temporal table. We declare these columns as system period columns on later step in this tutorial.

ALTER TABLE dbo.EmployeeMaster
ADD StartTime DATETIME2
GO

ALTER TABLE dbo.EmployeeMaster
ADD EndTime DATETIME2
GO

These columns must be of datetime2 datatype to avoid the errors like below when we declare these columns as system period columns to enable date change history on the table:

Msg 13501, Level 16, State 3, Line 23
Temporal generated always column ‘StartTime’ has invalid data type.

Step 3 – Update row effective from and to period values for existing rows

Now we need to update the row effective from and to values in these columns for each row as like below:

UPDATE dbo.EmployeeMaster SET StartTime = '19000101 00:00:00.0000000', EndTime = '99991231 23:59:59.9999999'
GO

We have updated the StartTime column by default datetime2 datatype value (1900-01-01 00:00:00) with 7 fractional second precision ‘19000101 00:00:00.0000000’. We can update it with a datetime value as required in our scenario. For example, we can also use GETDATE() function to start the row effective time as current date time value.

The end time value must be the SQL Server max date time value ‘99991231 23:59:59.9999999’. We have used the datatype datetime2 to declare the columns with default precision 7 for fractional second part.

In case we define the datatype as DATETIME2 (0), i.e. date type datetime2 with 0 precision for fractional second part, we can use ‘19000101 00:00:00’ and ‘99991231 23:59:59’ values respectively for start and end time period columns.

Its compulsory to update the end time column value as SQL Server max date value including the fractional second precision part value, otherwise it throws below error when we declare this column as system period end time:

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

Step 4 – Alter column to add NOT NULL constraint

We have added two columns and updated the values accordingly, but to use these columns as system period column, we must mark them not NULLABLE as like below.

ALTER TABLE EmployeeMaster
ALTER COLUMN StartTime DATETIME2 NOT NULL
GO

ALTER TABLE EmployeeMaster
ALTER COLUMN EndTime DATETIME2 NOT NULL
GO

In case of nullable columns, we get error message like below when we declare these columns as system period columns:

Msg 13587, Level 16, State 3, Line 23
Period column ‘StartTime’ in a system-versioned temporal table cannot be nullable.

Step 5 – Declare system period columns

Use below command to declare the above created columns as system period start time and end time periods:

ALTER TABLE dbo.EmployeeMaster
ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

Step 6 – Enable system versioning on the table

To change this table into a temporal table, we need to enable system versioning on this table. To do this we have SYSTEM_VERSIONING statement in SQL Server 2016 which needs to be set on. When we enable system versioning on a table, an another table is required to store time varying changes, to trace historical data. If we don’t want to go with a system generated name for the history table, we can define the history table name otherwise SQL Server automatically creates a history table, if name is not specified. We can also store an existing table as an history table.

ALTER TABLE dbo.EmployeeMaster
SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeMaster_History, DATA_CONSISTENCY_CHECK = ON))
GO

Conclusion

See the above image, we have converted the employee master table to a temporal table by enabling the system versioning on this table. If we execute an update or delete command on this table now, the data change history will be traced along with the time period.

For a test, we can use an update command and see the data changes as below:

--Update a record
UPDATE dbo.EmployeeMaster SET JobTitle = 'Research and Development Manager' WHERE BusinessEntityID = 2

--View the data in main and history table
SELECT * FROM dbo.EmployeeMaster
SELECT * FROM dbo.EmployeeMaster_History

Have a look on the below image to see the data change history in the table:

Thanks for the reading and please do share and rate this post if you like this. I would also request you to put your valuable input in below comment section of this blog post.