Basics of Temporal Tables in SQL Server 2016
Microsoft has introduced many new features with their new release of SQL Server 2016. One of the such interesting feature released with SQL Server 2016 is Temporal Tables. Temporal Tables are also known as system-versioned tables. In a single liner, these Temporal Tables allows SQL Server to automatically keep history of the data in the table instead of just the current data which we see in regular or temporary tables or variables. This Temporal Tables is a database feature, introduced newly with SQL Server 2016. Although this is called as system-versioned temporal table, but this is a new type of user table in SQL Server 2016, which is designed to keep a full history of data changes in the table and allows an easy point in time analysis of the data. You might wonder, why this type of temporal table is called as a system-versioned temporal table, it is because the period/time of validity for each row is managed by the system, which is the database engine.
Temporal tables were introduced in the ANSI SQL 2011 standard, which is now adopted by Microsoft SQL Server 2016 onwards. To understand more about the Temporal tables, we can compare it with regular user table to understand its behavior, which is different from a normal user table. In a normal user table, we insert the data and we can see the inserted data using select statement, but once we issue a delete or update statement, we will now see the latest data only with the new select statements, but with Temporal tables, we can query and get the data which was deleted or the data which was modified and what was the old value of the data. Taking a real time example, suppose we have a table with one column and it has one row with a data value of ABCD, now we updated this data from ABCD to ABC, now in a normal user table, we can only see ABC value, but with a Temporal tables we can actually see both the old value ABCD and ABC. This is possible by storing the history of the data changes and its values in a history table. History table has the old data and also the start and end times to identify when the data was present in the Temporal tables, thus allowing us to see the data at a older given point of time.
Now, as we have some basic idea about the Temporal tables, lets try to understand further about how to create and query these Temporal tables. Before, we proceed with seeing, how to create these Temporal tables, as like any other feature or object, there are some pre-requisites for creating Temporal tables.
– It is mandatory for a temporal table to have a primary key defined.
– Two additional columns are required to be defined while creating the temporal tables, which are used to record the start and end date and times. These two columns must be of datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.
– These tables cannot be part of In-memory OLTP feature.
– There are some restrictions on types of triggers that can be created on these tables.
CREATE TABLE dbo.TestTemporalTable (ID int primary key, Col1 int Col2 int 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(HISTORY_TABLE = dbo.TestTemporalTableHistory));
If we don’t specify and History_table, the SQL Server creates a history table with its own name like dbo.MSSQL_TemporalHistoryFor_xxx, where xxx is the object id of the main table. In GUI, the TestTemporalTable is shown under the tables list, where as the TestTemporalTableHistory will be shown under as a subset of TestTemporalTable. TestTemporalTableHistory has same columns as the actual temporal table, but any constraints will be removed. Also, we can create different indexes on TestTemporalTableHistory and TestTemporalTable tables based on the usage of these tables in the application and having different indexes, will greatly help improving the performance. Also, this is not compared to the CDC, which mainly uses transaction log file to track the changes.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings