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


Krishna KV
Team Leader, Aspire Systems
Published On :   08 Jul 2016
Visit Count
Today :  1    Total :   7215
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

Sharegate: Kick-Ass Tool
Think Your SharePoint & Office 365 Are Secure ? Find Out Now!


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.

SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

KWizCom Forms App