Understanding Temporal Tables — SQL
Auditing the database is one of the important parts of any enterprise application. Regulatory compliance is a critical aspect of the IT landscape these days, and the ability to audit database activities showing who did what to which data when is a specific requirement of many industry and governmental regulations.
There are many ways to audit the data in SQL,
Each will be having its own advantages and disadvantages. As the first two are there for years, We will see how we can use Temporal Tables to audit the data.
What is Temporal Table?
Temporal tables, also known as system-versioned tables, provide us with new functionality to track data changes. It allows SQL Server to maintain and manage the history of the data in the table automatically. This feature provides a full history of every change made to the data.
It was first introduced in the ANSI (American National Standards Institute) SQL 2011 standard. Now, it’s available in SQL Server 2016 and later versions.
Why Temporal Tables?
Data Audit
To store critical information for which you need to keep track of what has changed and when, and to perform data forensics at any point in time.
Point-In-Time Analysis
We can use time travel to see how entire data sets changed over time. Also Differences between two points in time of interest (a month ago vs. three months ago, for instance).
Repairing row-level data corruption
To quickly repair individual rows to any of the previously captured states. This property of temporal tables is very useful when you are able to locate affected rows and/or when you know the time of undesired data changes so you can perform repair very efficiently without dealing with backups.
How to Enable?
To enable Temporal Tables we have to consider the below points,
- The main table must have a primary key defined.
- It must have two columns Start Time & End Time declared GENERATED ALWAYS AS ROW of datatype datetime2
- It must have exactly one PERIOD FOR SYSTEM_TIME defined.
- The history table must always be schema-aligned with the current or temporal table, in terms of the number of columns, column names, ordering and data types. If the History table is not created by SQL.
How does it work?
When we create a Temporal table by turning ON the SYSTEM_VERSIONING, a new table will be created to store the History. Whenever an Update or Delete is done on the Main Table, Start Time in the main table will be updated and A new record (Data before the update) will be created in the Temporal Table with the Start-time & End Time.
/*State of entire table AS OF specific date in the past*/
SELECT [DeptID], [DeptName], [SysStartTime],[SysEndTime]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF ‘2020–02–01 T10:00:00.7230011’ ;
So the above query will provide the result of all the Department Data that is on 1st Feb 2020. (Even though the record is updated or deleted after that date.)
Microsoft has given clear documentation about how to create and manage the Temporal Tables. Please check the links in the Reference Links section.
Data Retention
Data Retention is one of the most important points we have to consider on the History Tables for stale data as it grows bigger very easily if the table is constantly updated, But Temporal tables provide us below options to do that
- Stretch Database
History tables can be stretched entirely or only a particular portion can also be stretched. - Table Partitioning
This can make large tables more manageable and scalable. Using the table partitioning approach, partitioning based on the Year, Month or Date based on the table usage. - Custom cleanup script
Writing a custom script to clean up the data. In order to avoid data inconsistency, perform cleanup either during the maintenance window (when workloads that modify data are not active) or within a transaction (effectively blocking other workloads). - History Retention Policy
You can configure the HISTORY_RETENTION_PERIOD when creating the temporal tables. So the rest is taken cared by the SQL Server. This is available only from SQL Server 2017.
Comparison of CDC Vs CT vs Temporal Tables
Below is the Feature table comparison collected from MSSQLTIPS.
