System-versioned temporal table OR temporal table is a new type of user table with data history. It maintains historical data like SCD-2 (Slowly Changing Dimension type2) .
This concept has introduced in ANSI SQL 2011 and incorporated by MS SQL Server in SQL Server 2016 onward. It’s a very great feature for auditing purpose.
Facts of Temporal Table:
Below mentioned features are tested and verified.
- It has two parts system-versioned temporal table and History table.
- Temporal table maintains only data level history.
- PRIMARY KEY is compulsory while defining system versioning.
- History table is read only.
- We can create Indexes/statistics in history table for better query performance.
- We cannot add column, alter datatype, add trigger, add constraint, add check constraint etc. in history table. More precisely, anything which requires schema changes or data restrictions are not allowed.
- It requires 2 additional columns with datetime2 datatype to store time-stamps (UTC date-time). Also, it doesn’t affect any DML operation as it is hidden for such statements.
- If user defined history table name already exists then it will become history table with old data.
- If we add column in existing temporal table then it will maintain history for it without any additional setting.
- We can drop column from temporal table even if it has SYSTEM_VERSIONING = ON and it loses the history for that column as well.
How to create Temporal Table:
Using below CREATE TABLE statement, we can create temporal table.
CREATE TABLE dbo.Test_TemporalTable ( ID INT IDENTITY PRIMARY KEY ,FName VARCHAR(50) ,FromDateTime DATETIME2 GENERATED ALWAYS AS ROW START ,EndDateTime DATETIME2 GENERATED ALWAYS AS ROW END ,PERIOD FOR SYSTEM_TIME (FromDateTime,EndDateTime) ) WITH (SYSTEM_VERSIONING = ON)
FromDateTime: Data history start date time.
EndDateTime : Data history end datetime.
SYSTEM_VERSIONG = ON: signifies that system versioning has enabled.
You can see on Object Explorer; “system-versioned” tag with temporal table. Which defines it as temporal table.
Where is the history table?
You can find the history table just beneath the temporal table in object explore with “History” tag. Also it has similar structure as temporal table but without primary key. Above CREATE TABLE statement has no user -defined name for history table so it will be create with prefix MSSQL_ and suffix object_id. In case, if the auto generated history table already exists then SQL Server will add a random number after it.
See below snap:
Insert/Update/Delete on system-versioned temporal table:
We can perform Insert/Update/Delete as like normal table. The additional columns are hidden for such operations.
In below SQL, we will create a temporal table with user defined History table and Insert/update/delete data on it.
-- Creating system-versioned table GO CREATE TABLE dbo.Test_TemporalTable ( ID INT IDENTITY PRIMARY KEY ,FName VARCHAR(50) ,FromDateTime DATETIME2 GENERATED ALWAYS AS ROW START ,EndDateTime DATETIME2 GENERATED ALWAYS AS ROW END ,PERIOD FOR SYSTEM_TIME (FromDateTime,EndDateTime) ) -- Enable system versioning ALTER TABLE Test_TemporalTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Test_TemporalTable_History)) -- Inserting test data into temporal table INSERT INTO Test_TemporalTable (FName) VALUES ('Data_01'),('Data_02') -- Currently no data into History table SELECT * FROM Test_TemporalTable SELECT * FROM [dbo].[Test_TemporalTable_History]
After inserting data into temporal table; we can find data only in temporal table whereas History table has no data in it because it has no change till now.
Now, let’s perform some DML operation in temporal table
-- DML operation -- Update ID=1 UPDATE Test_TemporalTable SET FName = 'Data_01_Updated' WHERE ID = 1 -- Delete ID=2 DELETE Test_TemporalTable WHERE ID = 2 -- Insert New data INSERT INTO Test_TemporalTable (FName) VALUES ('Data_03') -- Now History table has historical data whereas temporal table has current data. SELECT * FROM Test_TemporalTable SELECT * FROM [dbo].[Test_TemporalTable_History]
Now, we can see the data in history table. The time-stamps are signifying the data validation period.
How to query from temporal table:
We can see history data along with current data using below query.
SELECT * FROM dbo.Test_TemporalTable FOR SYSTEM_TIME BETWEEN '2017-01-01 00:00:00' AND '9999-12-31 23:59:59.9999999' ORDER BY ID, FromDateTime
Here, a new clause “FOR SYSTEM_TIME” has introduced in SQL 2016 to filter history time-stamp. You can apply normal WHERE clause after it.
How to drop temporal table:
To drop temporal table we need to OFF system versioning first then it will allow to drop the temporal table. See below code:
ALTER TABLE Test_TemporalTable SET (SYSTEM_VERSIONING = OFF) GO DROP TABLE dbo.Test_TemporalTable