Home > SQL Server 2016 > Time Travel with Temporal Tables in SQL Server 2016 – Part 2

Time Travel with Temporal Tables in SQL Server 2016 – Part 2


In my [previous post] I discussed about Temporal Data, how it will be supported in SQL Server and how to CREATE Temporal or System-Versioned Tables.
 

–> Here in this post we will see how we can enable System-Versioning in an existing table containing data. I will also tweak the data to show you the demo on how you can point to a time back in history and get details relative to that time. This will be like Time Travelling to past and seeing record details as if its current data, without much change to the current table’s data-structure.
 

1. Let’s create a sample table [dbo].[Employee] and populated it by JOINing some tables on [AdventureWorks] Database:

USE [TestManDB]
GO

;WITH CTE AS (
	SELECT 
		E.BusinessEntityID, P.FirstName, P.LastName, D.Name AS DepartmentName, 
		ROW_NUMBER() OVER(PARTITION BY E.BusinessEntityID ORDER BY D.ModifiedDate DESC) as RN

	FROM [AdventureWorks2014].[HumanResources].[Employee] E
	JOIN [AdventureWorks2014].[Person].[Person] P
	ON P.BusinessEntityID = E.BusinessEntityID
	JOIN [AdventureWorks2014].[HumanResources].[EmployeeDepartmentHistory] DH
	ON DH.BusinessEntityID = E.BusinessEntityID
	JOIN [AdventureWorks2014].[HumanResources].[Department] D
	ON D.DepartmentID = DH.DepartmentID
)
SELECT BusinessEntityID, FirstName, LastName, DepartmentName
	INTO dbo.Employee
FROM CTE
WHERE RN = 1
GO

 

2. The System Versioned Temporal table [dbo].[Employee] must have Primary Key defined: otherwise you will get the same error message (Msg 13553).

ALTER TABLE dbo.Employee 
	ADD CONSTRAINT PK_BusinessEntityID PRIMARY KEY (BusinessEntityID)
GO

 

3. Now to make [dbo].[Employee] table System Versioned we will add:

Two Audit columns of datetime2 datatype to store Start & End datetime.

– Use PERIOD FOR SYSTEM_TIME clause to associate these two columns as System Time.

ALTER TABLE dbo.Employee ADD 
	StartDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL 
		DEFAULT CAST('1900-01-01 00:00:00.0000000' AS DATETIME2),
	EndDate	  datetime2 GENERATED ALWAYS AS ROW END   NOT NULL 
		DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2),
PERIOD FOR SYSTEM_TIME (
	StartDate, 
	EndDate
)
GO

 

4. After all pre-requisites let’s enable the System-Versioning on [dbo].[Employee] table:

ALTER TABLE dbo.Employee 
	SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory))
GO

 

–> So, as soon you enable the System-Versioning the SQL Engine creates an another History table with similar schema and nests it under the main Temporal table, let’s check both the tables columns and contents:

SELECT TOP 10 * FROM dbo.Employee
SELECT TOP 10 * FROM dbo.EmployeeHistory
GO

SQL Server 2016 Temporal 08

You can see above the History table is empty as there are no updates on the Parent table. I’ve rounded the 5th row because I will update this row in next step for the demo.
 

5. Let’s make some updates on the parent Temporal Table (5th row): SQL Engine will automatically populate the History table.

UPDATE dbo.Employee 
SET FirstName = 'Gabriel'
WHERE BusinessEntityID = 5
GO

UPDATE dbo.Employee 
SET DepartmentName = 'Research and Development'
WHERE BusinessEntityID = 5
GO

UPDATE dbo.Employee 
SET DepartmentName = 'Executive'
WHERE BusinessEntityID = 5
GO

-- Let's check the records again:
SELECT * FROM dbo.Employee WHERE BusinessEntityID = 5
SELECT * FROM dbo.EmployeeHistory WHERE BusinessEntityID = 5
GO

You will see that after doing 3 UPDATEs on the parent Temporal Table the History table [dbo].[EmployeeHistory] is populated with 3 rows that contains the older versions on data in [dbo].[Employee] table across all columns.
 

6. Ok, now I’ll do some tweaks on the System Time column values of [dbo].[Employee] table.

– First of all I will switch OFF the System-Versioning on dbo.Employee table.

– Now I will update the date of System Time columns, set it to back in history (5-10 days back for an effective demo).

– Enable back the System-Versioning

ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF )
GO

update dbo.EmployeeHistory
set EndDate = '2015-06-01 18:47:07.5566710'
where BusinessEntityID = 5 AND EndDate = '2015-06-09 18:47:07.5566710'

update dbo.EmployeeHistory
set StartDate = '2015-06-01 18:47:07.5566710',
	EndDate = '2015-06-05 18:47:28.0153416'
where BusinessEntityID = 5 AND StartDate = '2015-06-09 18:47:07.5566710'

update dbo.EmployeeHistory
set StartDate = '2015-06-05 18:47:28.0153416'
where BusinessEntityID = 5 AND StartDate = '2015-06-09 18:47:28.0153416'
GO

ALTER TABLE [dbo].[Employee] 
	SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory))
GO

-- Check the data after datetime changes:
SELECT * FROM dbo.Employee WHERE BusinessEntityID = 5
SELECT * FROM dbo.EmployeeHistory WHERE BusinessEntityID = 5
GO

SQL Server 2016 Temporal 09
 

–> Ok, now here comes the most interesting part of the topic i.e. “Time Travel”:

SQL Server engine provides you FOR SYSTEM_TIME AS OF {datetime_value} option with your SELECT query to get details pointing to any time in the history, let’s check this here:

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-01-01'
WHERE BusinessEntityID = 5

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-06-03'
WHERE BusinessEntityID = 5

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-06-07'
WHERE BusinessEntityID = 5

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-06-10'
WHERE BusinessEntityID = 5
GO

The above four Queries will show you results from the History Table by pulling out the records for the particular date you mentioned by checking the date ranges in the Audit columns:

SQL Server 2016 Temporal 10
 

–> Let’s check the Execution Plan of the 4th SELECT statement:

SQL Server 2016 Temporal 11

So, you can see that you are just querying the dbo.Employee Temporal table, but SQL Engine is internally also querying the dbo.EmployeeHistory table and concatenating (UNION) the rows from both the operators.
 

–> Final Cleanup: before Dropping the Temporal & History tables, you need to switch OFF the System Versioning feature in the parent table.

ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Employee]
GO
DROP TABLE [dbo].[EmployeeHistory]
GO

 

–> You can also check this demo in this video:


  1. Glenn Brown
    December 15, 2016 at 7:11 am

    Nice work Manoj, although I’m still not clear on why the startdate and enddate columns need to have MIN and MAX defaults? i.e…

        DEFAULT CAST('1900-01-01 00:00:00.0000000' AS DATETIME2),
    
        DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2),
    

    ??

    • December 15, 2016 at 10:52 am

      Because while creating a new table, you need to have some range, thus min & max date. And as you insert new rows, and/or modify them new versions will be created between those date ranges.

  2. Vieriu Alexandru
    October 31, 2016 at 8:50 pm

    After i update dbo.EmployeeHistory( all three of them ) i got “0 rows affected). Couldn’t find an explanation on google. If i miss some theoretical knowledge please shear a link. Thank you for the videos.

    • October 31, 2016 at 10:43 pm

      Did you updated the EmployeeHistory records with correct date filters? Here the dates will be different, so you need to change the dates, what I provided above in my UPDATE query. ~Manoj

  1. January 5, 2017 at 12:04 pm
  2. August 7, 2015 at 7:01 am
  3. July 13, 2015 at 8:58 pm
  4. June 23, 2015 at 12:13 pm
  5. June 17, 2015 at 7:39 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: