NEW T-SQL FEATURES IN SQL SERVER 2016 – XI | Temporal Tables

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | Temporal Tables

Microsoft SQL Server 2016 introduced 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.

Notes:-

1. It is a user table designed to keep a full history of data changes.
2. Every temporal table has two explicitly defined columns Period start column and Period end column, each with a datetime2 data type
3. This allow easy point in time analysis and point in time recovery
4. It can also help us in maintaining SCDs
5. Apart from the datetime2 columns, a temporal table also contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table.

How it works:-

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.

**–Picture Courtesy – Microsoft

Now let’s take and example of temporal table.

Example of Temporal Table

/* Data Generation and Table Creation | How to create temporal table */

--

CREATE TABLE testtemporalTable
(
    Id INT PRIMARY KEY
  , Val Varchar(100)
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HistorytesttemporalTable));  
GO

--

/* Perform DML operations on testtemporalTable */

--

INSERT INTO testtemporalTable(Id,Val) VALUES 
(1,'P'),
(2,'Q'),
(3,'R'),
(4,'S'),
(5,'T')
GO

DELETE FROM testtemporalTable WHERE ID IN ( 1 , 3 ) 
GO

UPDATE testtemporalTable SET Val = 'Pawan' WHERE Id = 5
GO

INSERT INTO testtemporalTable(Id,Val) VALUES 
(1,'PO'),
(3,'RO')
GO

--

/* Check the data from testtemporalTable */

--

SELECT * FROM testtemporalTable
GO

Id          Val               ValidFrom                   ValidTo
----------- ----------------- --------------------------- ---------------------------
1           PO                2018-01-21 04:36:52.96      9999-12-31 23:59:59.99
2           Q                 2018-01-21 04:32:33.48      9999-12-31 23:59:59.99
3           RO                2018-01-21 04:36:52.96      9999-12-31 23:59:59.99
4           S                 2018-01-21 04:32:33.48      9999-12-31 23:59:59.99
5           Pawan             2018-01-21 04:36:23.29      9999-12-31 23:59:59.99

(5 rows affected)

--

/* Check the data from HistorytesttemporalTable */

--

SELECT * FROM HistorytesttemporalTable
GO

Id          Val              ValidFrom                   ValidTo
----------- ---------------- --------------------------- ---------------------------
1           P                2018-01-21 04:32:33.48      2018-01-21 04:35:55.48
3           R                2018-01-21 04:32:33.48      2018-01-21 04:35:55.48
5           T                2018-01-21 04:32:33.48      2018-01-21 04:36:23.29

(3 rows affected)


--

/* Note if you just insert the data and the data has not changed then that data will not be available in the history table of temporal table. Example below – */

--

INSERT INTO testtemporalTable(Id,Val) 
VALUES ( 6 , 'Pawan-9' )
GO

SELECT * FROM HistorytesttemporalTable
GO

Id          Val               ValidFrom                   ValidTo
----------- ----------------- --------------------------- ---------------------------
1           P                 2018-01-21 04:32:33.48      2018-01-21 04:35:55.48
3           R                 2018-01-21 04:32:33.48      2018-01-21 04:35:55.48
5           T                 2018-01-21 04:32:33.48      2018-01-21 04:36:23.29

(3 rows affected)

--

Refer Microsoft LINK below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com