SQL Server - Temporal Tables

SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2016.

The traditional databases store the data that is considered to be valid at the current time only, temporal tables provide correct information about stored facts at any point in time.

With the release of SQL Server 2016 it is important to keep full history of changes in the context of system (transaction) time, i.e. time when data modification became actual in the database.

In this case, RDBMS (SQL Server) is fully responsible for keeping immutable history and maintaining timestamps for current and previous record versions.

An Example for Temporal table:

-- Create database

CREATE DATABASE mi_temporalDB

GO

USE mi_temporalDB

GO

-- Simple temporal table with history table automatically created (name of history table is MSSQL_TemporalHistoryFor_565577053) :

CREATE TABLE dbo.TemporalEmp

(

       Emp_ID INT NOT NULL PRIMARY KEY,

       Emp_Name VARCHAR(100) NOT NULL,

       Valid_From DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,

       Valid_To DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL ,

       Period for system_time (Valid_From, Valid_To)

) WITH (SYSTEM_VERSIONING = ON)

GO

Once this table gets created, we will find the same in our Object Explorer tree as shown below:

You created a temporal table where the history table to track the data is created automatically.

For the sake of simplicity as we design temporal table it is implemented using two user tables related to each other. You can identify the related table by querying the DMVs:

SELECT

       name [Original_Table],

       OBJECT_NAME(history_table_id) [History_Table_Name]

FROM sys.tables

WHERE history_table_id IS NOT NULL

If you need to drop the table, it cannot be done till there is a related table. This can be done using the following command:

USE [mi_temporalDB]

GO

ALTER TABLE [dbo].[TemporalEmp] SET ( SYSTEM_VERSIONING = OFF )

GO

DROP TABLE [dbo].[TemporalEmp]

GO

-- Add the history table which was created in the database

DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_565577053]

GO

For more detailed information, please refer the below link published by Microsoft:

SQL Server 2016 - Temporal Tables

I hope this will help.