System Versioned Temporal Table for storing historical data in SQL Server 2016

Krishna KV
 
Team Leader, Aspire Systems
July 8, 2016
 
Rate this article
 
Views
9321

SQL Server 2016 gives support for temporal tables or system-versioned table. It’s a new type of system table which used us to keep the history of data changes. As per the implementation that we are going to have two table one with current values and another with the historical data.

The table should contain two columns as mandatory with the datatype datatime2. These columns used to refer the period. The temporal table will store the data when the update or deletion operation is performed on the parent table.

To create a table with the System versioned enabled

 CREATE TABLE Feedback(Id INT IDENTITY(1,1) PRIMARY KEY,Empid INT, Feedback VARCHAR(500) ,
 FeedbackFrom DATETIME2(2) GENERATED ALWAYS AS ROW  START,
 FeedbackTo DATETIME2(2)  GENERATED ALWAYS AS ROW END,
 PERIOD FOR SYSTEM_TIME(FeedbackFrom,FeedbackTo)
 )
 WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.Feedback_History))

clip_image002

The System versioned table will consits the same column information as the main table without constraints.

 INSERT INTO Feedback(Empid,Feedback) VALUES(1001,'Testing')
 
 SELECT * FROM Feedback
 SELECT * FROM Feedback_history

clip_image004

clip_image006

To view the table and related system versioned table name.

 SELECT name as 'TableName', OBJECT_NAME(HISTORY_TABLE_ID)as HistoryTable
  FROM SYS.TABLES WHERE HISTORY_TABLE_ID IS NOT NULL

clip_image008

If we need to drop the table, it cannot be done till there is a system versioned table is referred.

DROP TABLE IF EXISTS Feedback

clip_image010

To disable the system system-versioned table

 ALTER TABLE Feedback SET ( SYSTEM_VERSIONING = OFF )

This command will remove the system versioning and makes into two normal tables.

clip_image012

Creating a table with temporal tables these prerequisites is must.

· Primary key

· Two columns with datetime2. One with sysstarttime and sysendtime as not null type. Users are not allowed to update or insert the value. The column name doesn’t contain any restrictions.

· Instead of trigger should not be used.

· FileStream column type not supported.

Temporary table

· It cannot have constraints

· The data cannot be modified.

Category : SQL

Author Info

Krishna KV
 
Team Leader, Aspire Systems
 
Rate this article
 
Krishna K.V has been working in IT Industry for over 7+ years. He holds a Master Degree in Information Technology. He is more interested to learn and share new technologies, ...read more
 

Leave a comment