SQL with Manoj

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

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

–> 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:

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

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: