Temporal Tables in SQL Server 2016

Overview

SQL Server 2016 has introduced a new type of a table that allows full tracking of any changes that the primary table has undergone. It does this by creating a separate 'linked' table called a history table that records all modifications.The great benefit is that it allows the dba to know the state of the data at any point in time.Previously, if we needed to track changes to the table, the developer needed to create triggers and store the data in separate table. A time consuming task.
Some of the common uses of the temporal tables (also referred to as System-Versioned Temporal Tables) are as follows:
  • A mechanism to audit the data changes
  • Ability to recover from accidental data changes or accidental deletes
  • Slowly changing dimensions. A system-versioned table exactly behaves like a dimension with type 2 changing behavior for all of its columns.

How does temporal work?

System-versioning for a table is implemented as a pair of tables, a current table and a history table. Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:
  • Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.
  • Period end column: The system records the end time for the row in this column, typically denoted at the SysEndTime column.
The current table contains the current value for each row. The history table contains each previous value for each row, if any, and the start time and end time for the period for which it was valid.

Prerequisites

Before creating a new temporal table, a some of prerequisites must be met:
  • Must have a Primary Key
  • Must have two DATETIME2 columns representing the start and end period. If needed, these columns can be hidden using the HIDDEN flag. These columns are called the SYSTEM_TIME period columns.
  • Both start/end time columns cannot be NOT NULL.
  • By default, the history table is page compressed.
  • ON DELETE/UPDATE CASCADE is not allowed on the current table. This limitation has also been removed in SQL Server 2017 and higher.
  • Only AFTER triggers are only allowed on the current table.

Limitations

Some of the limitations are as follow:
  • Temporal and history table cannot be FILETABLE.
  • The history table cannot have any constraints.
  • INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.
  • Data in the history table cannot be modified.

Demo on how to create temporal tables

In this demo, we will see exactly how to create and query temporal tables from scratch and to create temporal on existing table.

--If exist drop database
USE master
IF EXISTS(select * from sys.databases where name='TemporalTabDB')
DROP DATABASE TemporalTabDB
--Create TemporalTabDB database
CREATE DATABASE TemporalTabDB
GO

--Create a temporal table from scratch.. When creating a temporal table, one more table is created called LocationHistory as defined in the (HISTORY_TABLE=dbo.LocationHistory)) option
USE TemporalTabDB
GO
CREATE TABLE Location
(
LocID INT NOT NULL PRIMARY KEY CLUSTERED,              -- Primary Key
Name VARCHAR(100),
StartTime DATETIME2 GENERATED ALWAYS AS ROW START,  -- starttime with datatype DATETIME2
EndTime   DATETIME2 GENERATED ALWAYS AS ROW END,    -- endtime with datatype DATETIME2
PERIOD FOR SYSTEM_TIME (StartTime, EndTime)         -- starttime and endtime columns here
)
WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.LocationHistory))  -- have the system versioning set to ON. (Certain commands will not execute while this property is set Off.)


--Insert a record into table
INSERT INTO location (LocID,Name,StartTime,EndTime)  -- mandatory datetime columns
VALUES (1,'Delhi',DEFAULT,DEFAULT)
GO
--View the data in temporal table
SELECT LocID, Name, StartTime, EndTime
FROM location


--View the data in temporal history table -- Notice, at this time there is no data, as no data has been modified!!
SELECT LocID, Name, StartTime, EndTime
FROM LocationHistory


--Let’s modify the data with an update statement by changing location from Delhi to Mumbai
UPDATE Location SET Name = 'Mumbai' WHERE LocID = 1
GO
--View the data in temporal table after modification
SELECT LocID, Name, StartTime, EndTime, 'CURRENT_TABLE' AS [CURRENT TABLE] -- Current data in a table
FROM Location
GO
--View the data in temporal history table -- Modification of the location from Delhi to Mumbai has been recorded in a history table.
SELECT LocID, Name, StartTime, EndTime, 'HISTORY_TABLE' AS [HISTORY TABLE] --Modified data recorded with date & time.
FROM LocationHistory


--What about another change?  How is that recorded?  Lets change location from Mumbai To Hyderabad
UPDATE [Location] SET Name = 'Hyderabad' WHERE LocID = 1
GO
--View the data in temporal table AFTER MODIFICATION
SELECT LocID, Name, StartTime, EndTime, 'CURRENT_TABLE' AS CURRENT_TABLE --<< CURRENT DATA IN TABLE
FROM [Location]
GO
--View the data in temporal history table -- Notice, a second record has been inserted to account for the modification
SELECT LocID, Name, StartTime, EndTime, 'HISTORY_TABLE' AS HISTORY_TABLE -- Recorded modified data with date & time.
FROM [LocationHistory]


--You can delete against the temporal table.
DELETE FROM [Location] WHERE LocID = 1
GO
--View the data in temporal table after modification
SELECT LocID, Name, StartTime, EndTime, 'CURRENT_TABLE' AS CURRENT_TABLE -- Current data in a table.
FROM [Location]
GO
--View the data in temporal history table -- Notice, one more records have been inserted to account for the modification
SELECT LocID, Name, StartTime, EndTime, 'HISTORY_TABLE' AS HISTORY_TABLE --Recorded modified data with date & time
FROM [LocationHistory]


--Notice you cannot delete from the history table as long as the SYSTEM_VERSIONING is set to ON
DELETE FROM dbo.locationHistory WHERE LocID = 1
GO


--If you want to delete from the history table. then you will have to turn OFF the SYSTEM_VERSIONING with the alter command
ALTER TABLE [Location] SET (SYSTEM_VERSIONING = OFF)
GO
DELETE FROM [LocationHistory] WHERE LocID = 1 -- Data deleted from LocationHistory table
GO
--View the data in temporal table AFTER MODIFICATION
SELECT LocID, Name, StartTime, EndTime, 'CURRENT_TABLE' AS CURRENT_TABLE -- CURRENT DATA IN TABLE
FROM [Location]
GO
--View the data in temporal history table -- Notice, more records have been inserted to account for the modification
SELECT LocID, Name, StartTime, EndTime, 'HISTORY_TABLE' AS HISTORY_TABLE -- Modified data recorded with date & time
FROM [LocationHistory]


--Drop Period definition
ALTER TABLE [Location]
DROP PERIOD FOR SYSTEM_TIME
GO
--Dropping both tables: location and locationHistory
DROP TABLE [Location]
DROP TABLE [LocationHistory]

Setting up temporal tables to existing tables

Let’s start with an ordinary table, and convert it into a temporal table. So I’ll create the Product table, and load it up with some data.

Use TemporalTabDB
go
--Enable the system versioning
CREATE TABLE Product (
ID int Identity,
ProductName varchar(50),
ProductPrice decimal(20,2));
GO
--Insert data
INSERT INTO Product VALUES ('SQLServer',495000);
GO
--View table. Notice, that the table is a regular table at this time as no system_versioning is applied
Select * from Product



To convert this into a temporal table, first I’ll add the two period columns and then I’ll enable temporal and set ProductHistory as the name of the history table.

--Alter table Product by adding settings primary key and setting date range
ALTER TABLE Product
ADD CONSTRAINT PK_ProductID PRIMARY KEY (ID),                  
BeginDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
DEFAULT SYSUTCDATETIME(),
EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL       
DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
PERIOD FOR SYSTEM_TIME (BeginDate,EndDate);                 
GO
--Setting the SYSTEM_VERSIONING = ON
ALTER TABLE Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));
GO
--View the data in temporal table after modification
SELECT Id, ProductName, ProductPrice, BeginDate, EndDate, 'CURRENT_TABLE' AS CURRENT_TABLE --Current data in a table
FROM Product
GO
--View the data in temporal history table
SELECT Id, ProductName, ProductPrice, BeginDate, EndDate, 'HISTORY_TABLE' AS HISTORY_TABLE --Modified data recorded with date & time.
FROM ProductHistory


--Insert data into a table
INSERT INTO Product (ProductName, ProductPrice, BeginDate, EndDate)
VALUES ('MySQL', 78000, default, default);
GO
--Update row in a table
Update Product Set ProductName = 'DB2' where Id = 2
GO
--View data in a current table
SELECT Id, ProductName, ProductPrice, BeginDate, EndDate, 'CURRENT_TABLE' AS CURRENT_TABLE --Current data in a table
FROM Product
GO
--View the data in temporal history table -- Notice, more records have been inserted to account for the modification
SELECT Id, ProductName, ProductPrice, BeginDate, EndDate, 'HISTORY_TABLE' AS HISTORY_TABLE --Modified data recorded with date & time.
FROM ProductHistory


--Insert 2 new records
INSERT INTO Product (ProductName, ProductPrice, BeginDate, EndDate)
VALUES ('MongoDB ', 12400, default, default);
INSERT INTO Product (ProductName, ProductPrice, BeginDate, EndDate)
VALUES ('BigData ', 355000, default, default);
GO
--Update 3 product price
UPDATE Product SET ProductPrice = 50000 WHERE ID = 1;
UPDATE Product SET ProductPrice = 50000 WHERE ID = 2;   
GO
--Delete NoSQL product
Delete Product WHERE ID = 3;
GO
--View data  --Table should have 8 Product, with pricing changes recorded
SELECT Id, ProductName, ProductPrice, BeginDate, EndDate, 'CURRENT_TABLE' AS CURRENT_TABLE --Current data in a table
FROM Product
GO
--View the data in temporal history table -- Notice, more records have been inserted to account for the modification
SELECT Id, ProductName, ProductPrice, BeginDate, EndDate, 'HISTORY_TABLE' AS HISTORY_TABLE --Modified data recorded with date & time.
FROM ProductHistory


--Query the contents of the Product table at any point in time: Find deleted row and inserting back to the main table.
SET IDENTITY_INSERT Product ON
Insert into Product (Id, ProductName, ProductPrice, BeginDate,       EndDate)
values (3, 'MongoDB' ,'12400', DEFAULT, DEFAULT)
GO
--View data  --Table should have 8 Product, with pricing changes recorded
SELECT Id, ProductName, ProductPrice, BeginDate, EndDate, 'CURRENT_TABLE' AS CURRENT_TABLE --Current data in a table
FROM Product
GO
--View the data in temporal history table -- Notice, more records have been inserted to account for the modification
SELECT Id, ProductName, ProductPrice, BeginDate, EndDate, 'HISTORY_TABLE' AS HISTORY_TABLE --Modified data recorded with date & time.
FROM ProductHistory
GO


--Turning off versioning
ALTER TABLE Product
SET (SYSTEM_VERSIONING = OFF);
GO
--Drop Period definition
ALTER TABLE dbo.Product
DROP PERIOD FOR SYSTEM_TIME
GO
--Drop Table
DROP TABLE dbo.Product
DROP TABLE dbo.ProductHistory


No comments:

Post a Comment