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
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:
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.
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
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…
??
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.