Implementing History Table or System Version Temporal Table to Track Data History
System-Versioned Temporal Tables, also known as History Tables are a built-in feature that keeps track of data changes in SQL Server. Unlike regular tables that only show the current data state, these tables help in seamlessly capturing and preserving the complete history of data changes. This innovative feature eliminates the need for developers to create complex triggers or manage separate tables for tracking historical updates or deletions. By effortlessly recording and managing data history, System-Versioned Temporal Tables empower database administrators and developers to gain deeper insights, simplify auditing processes, and ensure compliance with ease.
As we normally know Tables provide a static representation of data, displaying only the latest updates. The historical changes or deleted records are lost, posing significant challenges for auditing, analysis, and regulatory compliance. Developers needed to resort to manual techniques, such as triggers and auxiliary tables, to maintain a record of the data's evolution. These methods are cumbersome, error-prone, and lack efficiency.
Hence, Here SQL Server's System Versioned Temporal Table is an apt solution to the limitations mentioned before. As it provides built-in support to automatically track the complete history of data changes without any external script. Each row in the table is associated with two timestamp columns of the datetime2 data type. This timestamp indicates the period during which the particular version of the record was valid. And these timestamps are automatically updated with every modification. The main table that stores current data is referred to as the current table, or simply as the temporal table.
During temporal table creation, users can specify an existing history table (which must be schema compliant) or let the system create a default history table. Some of the use cases are,
With built-in history tracking of every data modification, auditing and compliance reporting task becomes a breeze increasing transparency and accountability.
This built-in feature helps to meet compliance standards and ensures adherence to regulatory guidelines.
It also provides a mechanism to Audit the data changes as the complete history is maintained Recovering from accidental data changes. For instance, if someone has wrongly deleted a record, because of the availability of the history data we can easily recover these deleted records.
Creating Temporal Table
The syntax for an example temporal Table creation is as below,
CREATE TABLE dbo.department
(
dept_id INT NOT NULL PRIMARY KEY CLUSTERED,
dept_name VARCHAR(50) NOT NULL,
manager_id INT NULL,
parent_dept_id INT NULL,
valid_from DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
valid_to DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.department_history));
Few points to remember while creating a System Versioned table.
The table must have a Primary Key, otherwise, it would raise the error.
The table must also have two DATETIME2 columns which store the start and end timestamps of the row's validity period. The "GENERATED ALWAYS AS ROW START" clause indicates the start time for the row, similarly "GENERATED ALWAYS AS ROW END" clause indicates the end time for the row.
The two columns that store the start time and end time for the rows shouldn't be null, otherwise, it will raise an error. Hence, the NOT NULL constraint is used.
The use of the HIDDEN flag for the period columns in System-Versioned Temporal Tables makes them effectively hidden from query results, ensuring a clean and streamlined view of the data. And when performing an INSERT INTO statement without specifying the column list, there is no need to provide values for these hidden columns in the values list. It's optional.
The PERIOD FOR SYSTEM_TIME(valid_from, valid_to) clause takes the names of the columns which was made for start and end timestamps. SQL Server Engine will use the columns to record the period for which a record is valid.
HISTORY_TABLE: This input parameter is used to specify the name of the History Table. And it's optional.
SYSTEM_VERSIONING: This argument is used to enable/disable system versioning on the table.
After executing the above statement, if we look into the SSMS window we can see that a dbo.department table was created and it is marked as a System-Versioned table. Inside this table we can see a nested table dbo.department_history table created and marked as History Table as shown in the below image:
DML operations on Temporal Table
Insert Operation:
Let us perform the insert operation to add a new record in the dbo.department table, with the statement below,
INSERT INTO dbo.department (dept_id, dept_name, manager_id, parent_dept_id) values (1, 'HR Department', NULL, NULL);
INSERT INTO dbo.department (dept_id, dept_name, manager_id, parent_dept_id) values (2, 'Sales Department', NULL, NULL);
Since the columns valid_from and valid_to are marked as HIDDEN, we can skip to specify it in the insert statement. If these columns were not marked as HIDDEN, then we would have to pass DEFAULT as values for the columns. Let's check the records in the dbo.department table and dbo.department_history table.
select * from department;
select * from department_history;
select dept_id, dept_name, manager_id, parent_dept_id, valid_from, valid_to from department;
We can see that the insert operation adds new records in the department (Temporal) table and not in the department_history history table. If you want to see the period's columns in the select statement, you need to mention it explicitly in the select statement. Since while creating the Temporal table, we marked these columns as HIDDEN it won't show unless mentioned explicitly like in the third select statement above.
In the image, which is showing the outputs of the select statements of the Temporal & History table also notice the periods columns valid_from and valid_to. The columns hold date-time value, valid_from represents the time of the execution of the insert statement and valid_to represents the maximum value of the DATETIME2 data type. This also indicates that the record is active.
Update Operation:
Now, let's try to update the department table to assign the manager_id value to HR Department and see how it reflects in the main (Temporal) table and History table.
update department set manager_id = 1 where dept_id = 1;
Again perform select queries on both department table and department_history table to check the output.
select dept_id, dept_name, manager_id, parent_dept_id, valid_from, valid_to from department;
select * from department_history;
In the above image showing the outputs, you can see that the update of a record in the temporal table has resulted in inserting the record in its history table with the state of the record before the update and the end time i.e. valid_to column value will be the time at which the update statement is executed. And if you noticed, the temporal table which only holds the current state of the record is updated and the manager_id value is assigned. But the important part is that it has also updated the valid_from column value to the time at which the update statement is executed. The record is still active and has the maximum value of the DATETIME2 data type for the valid_to column.
Important point to note that, you cannot update the PERIOD column values (valid_from, valid_to) when the SYSTEM_VERSIONING is ON. If you need to update the PERIOD columns then first we need to disable the system versioning and then drop the PERIOD definition from the department table, which will make the tables become like any regular table and can perform any operations.
Delete Operation:
Now we will perform a DELETE operation by deleting the record of the Sales department, which has dept_id of value 2.
DELETE FROM department WHERE dept_id = 2;
After executing the above statement, we will perform the select queries like earlier and see how the delete operation has caused any changes in the Temporal table and History table.
select dept_id, dept_name, manager_id, parent_dept_id, valid_from, valid_to from department;
select * from department_history;
As per the results shown in the image just above, we can see that the record for department id 2 which was the Sales department, was removed from the Temporal table, and the state of the record before the delete operation is stored in the History table with valid_to value showing when the delete operation has taken place.
Furthermore, it is important to note that, the TRUNCATE operation on the Temporal Table is not supported like regular tables. And as long as System Versioning is enabled, DML operation on the History table is not supported.
Drop Temporal Table:
To clean up and Drop Temporal and History Tables, we need to first disable the system versioning. And also need to drop the period definition. Below is the script based on our example.
-- disable system versioning
ALTER TABLE dbo.department SET (SYSTEM_VERSIONING = OFF)
GO
-- drop period definition
ALTER TABLE dbo.department DROP PERIOD FOR SYSTEM_TIME
GO
-- drop department and department_history tables
DROP TABLE dbo.department
DROP TABLE dbo.department_history
With this, we wind up the post about the intro for Temporal Table or SYSTEM-VERSIONED Table. To summarize, a system-versioned table, also called a temporal table, is a type of user-defined table to automatically keep track of the full history of data changes or mutations in table rows. Since it tracks the full history of data changes in the separate history table, with the database engine managing validity for each row, helping us to know the state of data at any given point in time in history. In the next post, we will look into Querying the System-Versioned Temporal Table and How to enable System Versioning for the existing regular table and more related topics.