Temporal Table in SQL Server

In this post we are going to discuss “Temporal Table”, a new feature introduced in SQL Server 2016. Lets start this post “Temporal Table in SQL Server” with the definition of temporal table.

Temporal Table facilitates inbuilt support for data change tracing along with the time period in a table. It holds the current as well as historical data to determine the values of a record at any given period of time. Temporal table uses a pair of tables; current table and an associated history table, to store current and historical data respectively. It uses two system columns of data type datetime2 to record start time and end time in both tables (current and history) to enable record versioning.

We can easily manage the data change history in a table using temporal tables which was a bit cumbersome and manual task in the previous versions of Microsoft SQL Server. Temporal tables are also called as system-versioned tables.

Data changes occurred due to an update, delete or merge operation on the table can be easily traced with temporal tables. Temporal tables can be used for multiple purposes. Few examples are as below:

1. To maintain a slowly changing dimension for data warehousing purpose.
2. To recover the accidental data changes caused as a result of a query which gets executed unintentionally.
3. Tracking data changes in a table along with the time period.
4. Tracing of data for auditing purposes and etc.

Create temporal table in SQL Server

We have an “Employee” table with EmployeeId, Name, EmpAddress, Contact, Designation, DateOfBirth, and Gender columns and we need to trace the data changes in the employee table.

Create the employee table with the mentioned columns as below to mark it as temporal or system versioned table:

CREATE TABLE dbo.Employee
(
 EmployeeId INT NOT NULL PRIMARY KEY,
 Name NVARCHAR(256) NOT NULL,
 EmpAddress VARCHAR(256) NOT NULL,
 Contact VARCHAR(20) NOT NULL,
 Designation VARCHAR(256),
 DateOfBirth DATETIME,
 Gender CHAR(1) NOT NULL,
 StartTime DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, --Marked as hidden to hide from SELECT list output
 EndTime DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, --Marked as hidden to hide from SELECT list output
 PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History, DATA_CONSISTENCY_CHECK = ON))

The last two columns in above code, column StartTime and EndTime which are used to store the row validity period. “WITH (SYSTEM_VERSIONING = ON)” statement marks the table as a temporal table. Using “SYSTEM_VERSIONING = ON” clause requires two additional columns of datetime2 datatypes in the table. We have used StartTime and EndTime to record the row validity along with the time period in our example.

The “(HISTORY_TABLE = dbo.Employee_History, DATA_CONSISTENCY_CHECK = ON)” is used to define the name of history table. In case, we don’t want to define an explicit name for history table, this statement is optional and SQL Server automatically creates a history table with some system generated name. We have used the above statement to define the name of the history table.

Both time period columns StartDate and EndDate has been marked as HIDDEN in main table which hides these columns from SELECT * column list output of main table.

Below is the image of temporal table along with its associated history table:

The red squared table named “dbo.Employee_History” which has all the columns as like main table has been created along with the main table to record any change in the data.

Insert rows in temporal table

Insert few sample data in the employee table and see the impact on both the tables (main and history table) below:

INSERT INTO dbo.Employee (EmployeeId, Name, EmpAddress, Contact, Designation, DateOfBirth, Gender)
VALUES(100, 'Albert', 'New Delhi', '0123456789', 'Senior Software Enginner', '19850106', 'M'),
(101, 'Michael', 'Mumbai', '2103456789', 'Team Lead', '19800608', 'M'),
(102, 'Scott', 'Chennai', '6789012345', 'Project Manager', '19750709', 'M')

Here are the entries in both the tables after execution of above insert statement:

We can see that in the above image, the current table “dbo.Employee” has the all three newly inserted rows but there is no any row in the history table, because there is no any change in the data as of now.

UPDATE rows in temporal table

Update few rows and see the impact of update statement on these tables. Below are the update statements:

UPDATE dbo.Employee SET Contact = '9876543210' WHERE EmployeeId = 100
GO
UPDATE dbo.Employee SET Designation = 'Senior Project Manager' WHERE EmployeeId = 101
GO

After update statement, we can see that the changes has been recorded in the history table in the below image:

DELETE rows from temporal table

Below we are going to delete employee record for employee id 100:

DELETE FROM dbo.Employee WHERE Employee_Id = 100
GO

Now have a look on the main table and the associated history table:

We can see that the record of employee id 100 has been deleted from current table, and in the history table the time period is showing the record has been deactivated.

Points to be considered before creation of temporal table:

1. We must define a primary key in the main table.
2. We must have period columns of data type datetime2 like start date and end date to store record validity period.
3. If the name of history table is being specified, it must use <schemaname>.<tablename> format. Otherwise we’ll receive an error as “Setting SYSTEM_VERSIONING to ON failed because history table ‘Employee_History’ is not specified in two-part name format.“

Limitations of temporal table

Below are some important limitations of temporal tables:

1. History table must be created on the same database and must follow two part naming convention in case of an explicit history table name. Optionally we can ignore to specify the history table name and in such case, system would name the table automatically.
2. Period columns can not be used in INSERT and UPDATE statements.
3. TRUNCATE table command can not be executed on temporal tables.
4. ALTER TABLE command is not allowed on temporal and the associated history table. Execution of below command would throw an error like “Cannot alter column ‘Name’ on table ‘SQLDB.dbo.Employee’ because it is not supported operation on system-versioned temporal tables“. Below commands are not allowed with ALTER TABLE on temporal and the history table associated with it:
   1. DROP COLUMN
   2. ADD COLUMN
   3. ALTER COLUMN
5. DROP TABLE command is not allowed on temporal tables and the associated history table.
6. INSTEAD OF TRIGGERs are not permitted on any of the tables (current or history table).

Reference: https://msdn.microsoft.com/en-us/library/dn935015.aspx

Thanks for the reading and please do rate and share this post on your social media. Don’t forget to share your thoughts in comment section of this post.