SQL Server has been providing a reliable and efficient database management service to the users for a long time. With every release of new version, the users are offered new features, which attracts them to use SQL Server for every data storage purpose. The latest version, SQL Server 2016 has emerged out with many new functional features, out of which temporal tables are the most discussed among they experts and users. In this section, we will learn about SQL Server temporal table and their usage in the database.
What is Temporal Table in SQL Server?
A temporal table is a kind of user table, which attempts to maintain the history of all the data changes done with the database. It is also known as system-versioned temporal table as the system itself is responsible to manage the validity period of each row. Each temporal table is associated with the following columns which belong to datetime2 data type. They hold the values for the validity of data for which the row has been modified. These columns are also known as period columns as they represent the time related parameters in datetime2 data type.
The temporal table in SQL Server holds a link to another table known as History Table, which carries all the previous versions of the data. So, the history table allows the user to retrieve the older values of any column using the temporal table.
Consider an example where, in a database the value of x=20 and the user has just updated the new value of x to 125. If there were no temporal table, the user will always get x=125 while retrieving from the main database. However, in many cases it becomes necessary for the user to get an idea of the previous records which have either been updated or deleted. So, the SQL Server temporal tables can make it easy for the user to easily access the previous versions of columns as it maintains the record in history table.
So, if the temporal table is used in above discussed example, then the temporal table can be easily used to retrieve older value of column i.e., x=20.
Role of Temporal Table in SQL Server 2016
Temporal table plays a very important role in maintaining the database records in a consistent way. An efficient use of the temporal table in SQL Server can help the user in the below mentioned ways:
- Data Forensics- Temporal tables provides an easy way to audit the data and understand how the values of a column has changed over the time. It can be used in data forensics to view all the changes made from history table.
- Data Reconstruction- One can easily view the nature of data change over the time using temporal table and reconstruct the data when needed.
- Data Recovery- In case of data corruption or server failure, the SQL Server temporal tables can be used to retrieve the older version of data and insert them back into the main table. So, it also acts like a backup.
- Research Purpose- The temporal tables can be used to study the change in values of the data for a time interval. It can help decision taking bodies to research and analyze the trends in data changes.
How to Create SQL Server Temporal Table
The temporal tables can be created for any database using a simple script. There are a few points which need to be taken care of while creating any temporal table:
- The primary key should be defined for the table
- Two columns must be created with datetime2 data type to hold the start and end date for records
- The temporal tables cannot have INSTEAD OF triggers
- The user can adopt AFTER trigger only on the current table
Consider the following script for creating a temporal table dbo.MyTemp, where two columns with datetime2 type are used to store the time records:
CREATE TABLE dbo.MyTemp (PID int primary key ,X int ,Y int ,Z AS X * Y ,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON);
Temporal tables serve as a significant role in SQL Server as they help to maintain a record track of all the changes done on the values of columns. The users are advised to make a judicious use of these tables. We hope that with this article, the major concept of SQL Server temporal table is made clear to the users.