SQL 2016 Temporal Tables.

Here is the situation for SQL DBA/Developer.

  1. A new startup company started with 5 employees, after an year no of employees grows to 100, subsequently 3-5 employees leaving in every month. Now DBA/Developer needs to get report of all employees those either worked OR currently working in organization. The other requirement is to get report to know all employees for those ‘first name’/’last name’ got change over the time.
  2. A product based company keep introducing new products and retires the old products from their list in regular interval. Now DBA needs to know the all products those were present in one specific old date.

All above situation can be done using complex front end code OR making lots of different tables for generating such reports. Now SQL 2016 comes up with new feature called “Temporal Table” that can help achieving above requirement with minimal code change. In this blog, I am going to show you how this can be done. Please note Temporal table in NOT same as Temporarily table.

Temporal Tables is “A system-versioned temporal table is designed to keep a full history of data changes and allow easy point in time analysis”

Here is list of prerequisite for temporal tables

  1. A primary key must be defined in the table.
  2. Two columns must be defined to record the start and end date with a data type of ‘datetime2’, these two columns can hide using ‘Hidden’ keyword.

Let’s now move to SQL 2016 LAB to see how this feature works.

USE [master]
CREATE DATABASE TemporalTableDemo
GO 

USE [TemporalTableDemo]
CREATE TABLE EmployeeData 
(
EMPID INT PRIMARY KEY,  ---Primary key is required
Firstname  VARCHAR(50),
LastName  VARCHAR(50),
City VARCHAR(50), 
/*Period columns needed for temporal tables*/
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo)
)
/*The system-versioning configuration needed for temporal If we don’t specify the history table then system will create automatically dbo.MSSQL_TemporalHistoryFor_XXX
*/
WITH (SYSTEM_VERSIONING = ON (History_Table=[dbo].EmployeeDataHistory)
GO

This is how Temporal table schema should look like.

TableStruture.jpg

Now let’s insert 8 rows.

USE [TemporalTableDemo]
INSERT INTO EmployeeData (EMPID, FIRSTNAME, LASTNAME, CITY) VALUES
              (1, 'Employee1', 'Lastname1', 'city1'), 
              (2, 'Employee2', 'Lastname2', 'city2'), 
              (3, 'Employee3', 'Lastname3', 'city3'),
              (4, 'Employee4', 'Lastname4', 'city4'),
              (5, 'Employee5', 'Lastname5', 'city5'), 
              (6, 'Employee6', 'Lastname6', 'city6'),
              (7, 'Employee7', 'Lastname7', 'city7'), 
              (8, 'Employee8', 'Lastname8', 'city8')
GO

Now let’s retrieve all the rows to confirm the records are inserted.

/*Select all employees data */
USE [TemporalTableDemo]
SELECT * FROM EmployeeData
GO 

/*Above select statement will not return all columns due to hidden keyword in table definition, however you can manually give columns.
*/
USE [TemporalTableDemo]
SELECT EMPID,Firstname, LastName, City, ValidFrom, ValidTo FROM EmployeeData
GO

Let assume few employees left over the time, new employees joined & some of employees details such as first name, last name OR city got changed.

USE [TemporalTableDemo]
DELETE FROM EmployeeData WHERE Firstname IN ('Employee1', 'Employee2')  
UPDATE EmployeeData SET LastName = 'ChangedLastName3' WHERE Firstname = 'Employee3'
UPDATE EmployeeData SET LastName = 'ChangedLastName4' WHERE Firstname = 'Employee4' 
INSERT INTO EmployeeData  (EMPID, FIRSTNAME, LASTNAME, CITY) VALUES
              (9, 'Employee9', 'LastName9', 'City9'), 
              (10, 'Employee10', 'LastName10','City10') 
GO

The reference of every update OR delete record is inserted in history table and DBA can query historical data to do point in time analysis.

Example 1 All current employees.

USE [TemporalTableDemo]
SELECT  EMPID, Firstname, LastName, City FROM EmployeeData
GO

Example 2 All current employees for whom details such as first name, last name OR city got changed over time.

USE [TemporalTableDemo]
SELECT  
EmployeeData.EMPID, EmployeeDataHistory.Firstname, EmployeeDataHistory.LastName, EmployeeDataHistory.City, EmployeeDataHistory.ValidTo as UpdatedON 
FROM EmployeeData
INNER JOIN EmployeeDataHistory on EmployeeDataHistory.EMPID=EmployeeData.EMPID
GO

Example 3 All Employees those left company.

USE [TemporalTableDemo]
SELECT  
EmployeeData.EMPID, EmployeeDataHistory.Firstname, EmployeeDataHistory.LastName, EmployeeDataHistory.City, EmployeeDataHistory.ValidTo as UpdatedON 
FROM EmployeeData
INNER JOIN EmployeeDataHistory on EmployeeDataHistory.EMPID=EmployeeData.EMPID
GO

Example 4 The temporal table can also be queried with a new clause ‘FOR SYSTEM TIME’ such as example below. More details

USE [TemporalTableDemo]
SELECT EMPID,Firstname, LastName, City FROM EmployeeData
FOR SYSTEM_TIME AS OF '2017-03-03 09:52:07.5372573'
--OR use between
--FOR SYSTEM_TIME BETWEEN '2017-03-03 09:52:07.5372573' AND
'2017-03-03 09:59:01.5704154'
GO

Limitation with temporal tables.

  1. Temporal and history table cannot be FILETABLE.
  2. The history table cannot have any constraints.
  3. INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.
  4. Data in the history table cannot be modified.

Here is the example of power-shell script that generate HTML report for all above queries for better understanding.

#Script start,  
#Change FilePath & SQL server name 
$SQLServer='LABSQL'
$FilePath='C:\temp\CurrentEmployee.HTML'
$connectionString = "Data Source=$SQLServer;Integrated Security=true;Initial Catalog=TemporalTableDemo;"
$connection= New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query1= “SELECT  EMPID, Firstname, LastName, City FROM EmployeeData”
$query2= “SELECT  EmployeeData.EMPID,EmployeeDataHistory.Firstname, EmployeeDataHistory.LastName,
EmployeeDataHistory.City, EmployeeDataHistory.ValidTo as UpdatedON 
FROM EmployeeData INNER JOIN EmployeeDataHistory on EmployeeDataHistory.EMPID=EmployeeData.EMPID”
$query3= “SELECT EmployeeDataHistory.EMPID, EmployeeDataHistory.Firstname, EmployeeDataHistory.LastName, EmployeeDataHistory.City, EmployeeDataHistory.ValidTo as TerminatedOn 
FROM EmployeeDataHistory LEFT JOIN EmployeeData ON EmployeeData.EMPID = EmployeeDataHistory.EMPID
WHERE EmployeeData.EMPID IS NULL"
$command1= $connection.CreateCommand()
$command1.CommandText =$query1 
$result1= $command1.ExecuteReader()
$table1= new-object "System.Data.DataTable"
$table1.load($result1)
$command2= $connection.CreateCommand()
$command2.CommandText =$query2
$result2= $command2.ExecuteReader()
$table2= new-object "System.Data.DataTable"
$table2.load($result2)
$command3= $connection.CreateCommand()
$command3.CommandText =$query3
$result3= $command3.ExecuteReader()
$table3= new-object "System.Data.DataTable"
$table3.load($result3) 
$Header
= @"
<style>
TABLE {border-width:4px;border-style: solid;border-color: black;border-collapse: collapse;}
TH {border-width:1px;padding: 3px;border-style: solid;border-color: black;background-color:#FE642E;}
TD {border-width: 1px;padding:3px;border-style: solid;border-color: black;}
</style>
"@
$frag1= $table1| SELECT EMPID, Firstname, LastName, City | ConvertTo-HTML -PreContent '<H2> Details of current Employees</H2>' |Out-String
$frag2= $table2| SELECT EMPID, Firstname, LastName, City, UpdatedON |ConvertTo-HTML -PreContent '<H2> Updated Employees</H2>' |Out-String
$frag3= $table3| SELECT EMPID, Firstname, LastName, City, TerminatedOn |ConvertTo-HTML -PreContent '<H2> All Terminated Employees </H2>'|Out-String
ConvertTo-Html -Head $Header -PostContent $frag1,$frag2,$frag3 -PreContent '<h1>Employees Report<h1>' |Out-File $FilePath
$connection.Close()
#Script Ends, The output should look like below. 
HTML report.jpg
Advertisements

#pointintimeanalysis, #sql2016, #sqlaudit, #temporaltable