SQL Server 2016 - Temporal Tables

Temporal Tables:

Temporal tables are nothing but maintaining historical information for the rows by the SQL server itself for the given table.

Instead of implementing SCD for the tables by the developers, if you create the table as Temporal Table then SQL Server automatically will take care of maitaining the history for the changing data.

To Create the temporal tables we should use below syntax (Here i have created Department table):

CREATE TABLE dbo.Department ( DepartmentID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, DepartmentName varchar(50) NOT NULL, ManagerID int NULL, ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT CAST('1900-01-01' AS DATETIME2) , ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31' AS DATETIME2) , PERIOD FOR SYSTEM_TIME ( StartDate, EndDate ) ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory) ); GO

1. Two additional Start & End date Audit columns of datetime2 datatype for capturing the validity of records. You can use any meaningful column name here, we will use StartDate and EndDate column names

2. Both the column names have to be specified in PERIOD FOR SYSTEM_TIME (StartDate, EndDate) clause along with the columns.

3. Specify WITH (SYSTEM_VERSIONING = ON) option at the end of the CREATE TABLE statement with optional (HISTORY_TABLE = <<History_Table_Name>>) option.

Once you create the table it will automatically create two tables internally

one for current data and other for the historical data.

When you are querying the table, you can mention the table name that you have mentioned in Create table statement that will automatically call historical table internally and will return the results.

If we want to see only historical records then we can query the historical table that we have mentioned in our create table script.

If there are no updates to the data then no rows will be available in the historical table.

Limitation of Temporal Tables:

1. We can't query Temporal tables using Linked Server

2. History table should not have constraints (PK, FK, Table or Column constraints).

3. INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.

4. TRUNCATE TABLE is not supported till SYSTEM_VERSIONING is ON and we can truncate if we Switch OFF this setting

5. Direct modification of the data in a history table is not permitted.

6. INSTEAD OF triggers are not permitted on either the tables.

7. Usage of Replication technologies is limited.

For more information refer Temporal tables Part 1 and Part 2