Let’s Talk About Temporal Tables
If you are tired of time-consuming, resource-draining and ever-breaking queries that keep track of your data changes, you’re not alone.
Luckily, a tool was introduced in SQL server 2016 to deal with this: Temporal tables.
Also called system-versioned tables, this feature is designed to keep a log of modified and deleted data automatically and easily.
Let’s take a closer look!
How can we use Temporal tables?
The most common use cases for temporal tables include:
Slowly-changing dimensions
A clean approach to querying changes in data. This is ideal for businesses with processes based on stages, steps, or statuses such as mortgaging companies or subscription-based businesses.
Point-in-time analysis
Analyzing the state of the data at a particular point in time to examine it as it was initially reported, this is particularly the case with financial reports and statements.
Trend analysis
Examining data movements over time and calculating trends relative to data changes.
Audit
Creating an auditing path by keeping track of changes as well as the time they occurred which facilitate data forensics operations and the fulfillment of compliance standards.
Backup and recovery
Temporal tables could also be used as a method of retrieving data losses or fixing discrepancies with little to no downtime.
How do we implement it?
Let us start with the basics. System-versioned tables consist of the following two components:
Temporal (current) table: These tables represent the main dataset which reflects the current state of the data, comprising only the most up-to-date values.
Historical (history) table: These tables contain several rows per item as they automatically log all the changes in the current dataset.
Once the temporal table is created, a historical table will be systematically generated.
It’s time to create a temporal table!
Doing so may seem complicated [it isn’t, I promise], but follow along with the steps below and you will be well on your way to simplifying your versioning process!
Creating a new temporal table
Using mortgaging data as an example, let us create the “LoanData” table and set the SYSTEM_VERSIONING attribute to ON. One thing to keep in mind is that a primary key column must be assigned to the temporal table, though, no constraints - including a primary key, could be applied to the historical table.
Additionally, two columns of the datetime2 type must be created as these allow us to keep a log and easily query data modifications.
During this step, we will also populate the current table with two dummy loan numbers for demonstration purposes.
Click here to expand code block #1
Creating the current table with system versioning automatically creates a historical table
CREATE TABLE [dbo].[LoanData]
(
A PRIMARY KEY is required on the current table and not allowed on historical table
[LoanNumber] INT IDENTITY PRIMARY KEY
,[CustomerID] NVARCHAR(6)
,[Stage] NVARCHAR(40)
,[LoanOfficerID] NVARCHAR(5)
,[UnderwritterID] NVARCHAR(5)
,[ProcessorID] NVARCHAR(5)
,[DateSubmitted] DATETIME
,[DateReviewed] DATETIME
,[DateProcessed] DATETIME
,[DateUnderwritten] DATETIME
,[DateSetteled] DATETIME
,[LoanAmount] MONEY
Two DATETIME2 type start time and end time columns are required in order to log.
The time modifications occur.
,[SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
,[SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
The two columns are assigned as the system versioning period columns
,PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
The following statement creates/assigns the historical table.
It is preferable to name the table otherwise an [ugly] system generated name will be assigned
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LoanDataHistory)
)
GO
INSERT INTO [dbo].[LoanData]
([CustomerID],[Stage],[LoanOfficerID],[UnderwritterID],[ProcessorID],[DateSubmitted],[DateReviewed],[DateProcessed],[DateUnderwritten],[DateSetteled],[LoanAmount])
VALUES ('055123','Processed', '01193', '02044', '03019','2020-11-25'
,'2020-11-28','2020-12-05',NULL,NULL,549000)
, ('049034','Submitted', '01125', '02014', '03019','2020-12-05',NULL,NULL,NULL,NULL,319000)
GO
SELECT * FROM [dbo].[LoanData]
SELECT * FROM [dbo].[LoanDataHistory]
As you can see in the results below, both tables contain the same schemas. The temporal table (top) displays the two rows we inserted while the historical table (bottom) remains empty as all values are still current and no changes have taken place yet.
At this point, let’s assume we received the following instructions:
Loan number 1 has been moved out of underwriting and back to the loan officer to get it ready for settlement.
Loan number 2 was found to be created by mistake during a training session and needed to be deleted.
We perform the operations below to mimic these updates on the [LoanData] table.
Click here to expand code block #2
UPDATE [dbo].[LoanData] SET DateUnderwritten = '2020-12-08', Stage = 'Underwritten' WHERE LoanNumber = 1
DELETE FROM [dbo].[LoanData] WHERE LoanNumber = 2
GO
SELECT * FROM [dbo].[LoanData]
SELECT * FROM [dbo].[LoanDataHistory]
The results show us that both changes have been properly logged in the historical table [LoanDataHistory], while the row associated with loan number 2 is removed from the current table.
One problem remains, Loan number 2, which was mistakenly created should not actually exist in the history table and needs to be removed. Next, we will explore how to do that.
Deleting from the history table:
The catch here is that explicit modifications in the historical table are not permitted. Therefore, a simple DELETE statement would not take care of our issue.
In this case, the system versioning will need to be set to OFF before the deletion takes place then set back to ON to continue normal versioning. We need an effective way to perform this transaction quickly and dynamically.
One way to do this is by creating a custom parameterized stored procedure which could be easily executed whenever a similar issue is encountered.
Click here to expand code block #3
CREATE PROCEDURE [dbo].[LoanHistoryDelete] (@Id int)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
BEGIN TRANSACTION
ALTER TABLE [dbo].[LoanData] SET ( SYSTEM_VERSIONING = OFF )
SET @sql = 'DELETE FROM [dbo].[LoanDataHistory] WITH (TABLOCKX)
WHERE [LoanNumber] = ''' + CAST(@Id AS VARCHAR(40)) + ''''
EXEC (@sql)
ALTER TABLE [dbo].[LoanData] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[LoanDataHistory]))
COMMIT TRANSACTION
END
This procedure works by passing the loan number we need to delete - in this case 2, as a parameter which is then used in a delete statement we run for a short period during which the system versioning is turned OFF.
EXEC [dbo].[LoanHistoryDelete] 2
Once executed the row is removed from the historical table and the stored procedure is kept for later use.
Adding a temporal table to an existing table
You might want to ask if it is possible to add system versioning to an existing table.
The answer is yes, and the process is very similar to creating a new table with temporal capacities as long as the table you are adding the versioning to contains a primary key.
We merely need to add the start and end datetime2 columns to the table, declare them as system period columns, and activate the system versioning attribute.
Important note: the start time and end time values cannot be NULL and therefore need to be defaulted to the current date including the fractional second precision part, using the built-in function SYSUTCDATETIME() and SQL Server’s maximum date time value “9999-12-31 23:59:59.9999999”
Click here to expand code block #4
CREATE TABLE dbo.PropertyDetails (
LoanNumber INT NOT NULL PRIMARY KEY CLUSTERED
, PropertyType NVARCHAR(50) NOT NULL
, [Address] NVARCHAR(255) NOT NULL
, [Address2] NVARCHAR(255) NULL
, CityName Nvarchar(50) NOT NULL
, [State] Nvarchar(2) NOT NULL
, ZipCode Nvarchar(5) NOT NULL
, ZipExtension Nvarchar(4) NULL
)
GO
INSERT INTO dbo.PropertyDetails (LoanNumber, PropertyType, [Address], [Address2], CityName, [State], ZipCode, ZipExtension )
VALUES ( 1,'single family - detached','123, Main street',NULL,'Hillwood','CA','55555',NULL)
,(2,'apartment','456, 1st street',NULL,'Tempoville','WA','77777','4321')
GO
ALTER TABLE dbo.PropertyDetails
ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START
DEFAULT SYSUTCDATETIME() NOT NULL
,SysEndTime datetime2 GENERATED ALWAYS AS ROW END
DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.9999999') NOT NULL
,PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
GO
ALTER TABLE dbo.PropertyDetails
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PropertyDetailsHistory))
GO
SELECT * FROM dbo.PropertyDetails
SELECT * FROM dbo.PropertyDetailsHistory
There you have it! The table becomes a system versioned table!
Conclusion
The temporal table is a very valuable tool that automates row-versioned processes, streamlines data archiving, and facilitates slowly changing dimension operations. In combination with how easy it is to implement; this feature is a keeper!
For more details, visit the Microsoft documentation or reach out to our team at info@campfireanalytics.com.
[All the code used to create this example Temporal table is available here on Github.]