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

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))

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

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

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

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.

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.