Introduction to Temporal Table in SQL Server 2016

In the world of computing, there exist different relational database management systems for managing bulk data of users. One such database application was developed by Microsoft, named as Microsoft SQL Server. The MS SQL Server is an application that store and retrieve data of different software, which runs either on a machine or over a network. Until now, different SQL Server editions have been initiated & are in use, and the latest version is SQL Server 2016.

The recent edition is Microsoft SQL Server 2016 that was released on June 1, 2016. There is a new exciting characteristic embedded with this software known as SQL Server Temporal Table or system-versioned tables. This feature allows SQL server for automatically creating history of data in a tablur form. In this article, we are going to discuss about SQL Server 2016 temporal tables in brief with all its functioning provided by it.

More About Temporal Table in SQL Server 2016

Temporal tables is officially released in SQL Server 2016 but was firstly introduced in ANSI SQL 2011. It is now being supported by SQL Server 2016 and is being enhanced in SQL Server 2016 CTP2.1. Basically, it is an assertion table that holds the entire records of the server’s data. Based on the physical dates, the record of this table can be deleted or updated. Moreover, the feature of temporal table in SQL Server 2016 is not a replacement of CDC i.e. Change Data Capture feature. CDC features requires transaction log for determining the modification and these modifications are kept for short duration. On the other hand, SQL Server temporal tables archive the original modification in its history table and are proposed to stay for long duration.

Creating a Temporal Table in SQL Server 2016

Before performing creation of a new temporal table, there are some prerequisites that are to be fulfilled before creating the table:

• There must be a primary key
• There must be two columns in the server table for recording start & end date with datetime2 data type and these columns are known as SYSTEM_TIME period columns. By making use of HIDDEN flag, these period columns can be hidden.
• Out of two triggers i.e. INSTEAD OF & AFTER triggers, you are allowed to use AFTER triggers on current table, not INSTEAD OF triggers.
• Last prerequisites is that In-memory online transaction processing is not allowed

After fulfilling the above-mentioned prerequisites, create a new & simple system-versioned table by using following script:

NOTE: If user will not mention any name for history table, then SQL server will itself create following naming structure:

Following snapshots gives you knowledge about the output of newly created temporal table (after executing the script):

The history table is having a unique column set, which does not has any constraints embedded with it. Although, it has its own indexes set and statistics but for increasing the performance, users can create their own indexes on the history table. On an existing table, it is possible to enable temporal tables. Therefore, users can either have an existing history table & perform modification in that table or can create a new temporal table. To understand more clearly, go through the following example:

Different Operations of SQL Server Temporal Tables

• • For performing insertion in temporal tables, one will have to run the following script:

The output of this script is given in following snapshot:

• • Now if users want to delete one row from the table and update another, then use the following script:

After executing the modification script, the resultant will be:

• When temporal table is activated, table alterations are limited and the allowed modifications are:
  1. ALTER TABLE _ REBUILD
  2. CREATE INDEX
  3. CREATE STATISTICS

Apart from above-mentioned modification schema, other modifications are not allowed. For example: Drop operation can not be performed on the temporal table and if one tries to use drop command, then they will come across the following error messages:

Now a question arises: What to do when user wants to add a new columns in the table?

Well, for performing addition/alteration operation in temporal table, one will have to disable system-versioning by executing the following command:

This command will delete the system_versioning & convert the main and history tables into regular tables. Now in these regular tables, you can now perform any addition/alteration activity. Keep in mind that synchronization and history should be consistent throughout the session. After completing with all your modifications, turn on the temporal tables by using following script:

Conclusion

After learning all about temporal table in SQL Server 2016, one can conclude with the fact that this feature maintains a record of data in form of a table. Therefore, SQL Server provide its user with an ease of keeping a track record of its history in a tabular form.