Introduction to SQL Server Temporal Tables

On a recent project we needed to add the ability to track the history of changes to user records in an application. This application was already in maintenance mode, so we also wanted to minimize the number of code changes to implement so we ended up using a SQL Server feature called Temporal Tables which is sometimes also called system versioned tables. This feature adds a history table which allows you to query the state of a specific row at a specific time or between two timestamps. Additionally, the table behaves exactly like a normal table for non-temporal queries, so no changes were required in the application code to support this.

Creating a temporal table or system versioned table is largely the same as a regular table with just a couple of other requirements. Consider the following example table:

Part 1 – Introduction to SQL Server Temporal Tables

Part 2 – Converting an Existing SQL Server Temporal Table

Part 3 – SQL Server Temporal Tables Advanced Query Strategies

CREATE TABLE [dbo].[ApplicationUser]
(
	[UserId]			nvarchar(20)	NOT NULL,
	[FirstName]			nvarchar(100)	NOT NULL,
	[LastName]			nvarchar(100)	NOT NULL,
	[JobTitle]			nvarchar(100)	NOT NULL,
	[LastUpdatedBy]		nvarchar(20)	NOT NULL,
	CONSTRAINT [PK_ApplicationUser] PRIMARY KEY CLUSTERED ([UserId] ASC),
);

To make this as a temporal table you just need two add two datetime2 timestamp columns to act as row start and end timestamps and then configure the table to use system versioning.

CREATE TABLE [dbo].[ApplicationUser]
(
	[UserId]		nvarchar(20)	NOT NULL,
	[FirstName]		nvarchar(100)	NOT NULL,
	[LastName]		nvarchar(100)	NOT NULL,
	[JobTitle]		nvarchar(100)	NOT NULL,
	[LastUpdatedBy]	nvarchar(20)	NOT NULL,
	[ValidFrom]		datetime2(7) GENERATED ALWAYS AS ROW START	NOT NULL,
	[ValidTo]		datetime2(7) GENERATED ALWAYS AS ROW END	NOT NULL,
	CONSTRAINT [PK_ApplicationUser] PRIMARY KEY CLUSTERED ([UserId] ASC),
	PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[ApplicationUserHistory], DATA_CONSISTENCY_CHECK=ON));

With this updated table you can interact with it just as the original, your inserts, updates and merges are all the same. You have two new columns, but you don’t update those directly, they are automatically updated. At this point we have satisfied our original goal of adding tracking record history. The application code that adds or updates records didn’t need to change and the code that retrieves data for display is none the wiser to the change.
The next question is how do we retrieve the history of user records? If you do a simple query such as SELECT * FROM [ApplicationUser] WHERE UserId = @userId all I will see is the current state of the record with the addition of knowing when it was last updated by the ValidFrom column. The answer is to start doing time-based queries, for which there are 5 operators: all, as of, between, from/to, and contained in.

All

Let’s say we wanted to know all the changes for a record or subset of records. We can take our existing query and add FOR SYSTEM_TIME ALL and now our query will return all of the…

SELECT * FROM [ApplicationUser] FOR SYSTEM_TIME ALL WHERE UserId = @userId

AS OF

The AS OF operator is handy for retrieving what was the valid record at a specific time…

SELECT * FROM [ApplicationUser] FOR SYSTEM_TIME AS OF '2022-11-02 19:45' WHERE UserId = @userId

BETWEEN

The between operator will select all records that became valid on or after the start time and before or exactly on the end time…

SELECT * FROM [ApplicationUser] FOR SYSTEM_TIME BETWEEN '2022-11-02 19:41:49.4670179' AND '2022-11-02 19:54:16.0095193' WHERE UserId = @userId

FROM/TO

The from/to operator behaves nearly identically to the between operator, the only difference is that a record that became valid at the moment of the specified end time is not included…

SELECT * FROM [ApplicationUser] FOR SYSTEM_TIME FROM '2022-11-02 19:41:49.4670179' TO '2022-11-02 19:54:16.0095193' WHERE UserId = @userId

CONTAINED IN

Our final operator, contained in, will return records that became active and stopped being active within the times specified…

SELECT * FROM [ApplicationUser] FOR SYSTEM_TIME CONTAINED IN ('2022-11-02 19:41:49.4670179', '2022-11-02 19:54:16.0095193') WHERE UserId = @userId

Conclusion

With a fairly simple database change, we’ve been able to meet our requirement of adding user record changes and done so while not making big changes to the application code.

Part 1 – Introduction to SQL Server Temporal Tables

Part 2 – Converting an Existing SQL Server Temporal Table

Part 3 – SQL Server Temporal Tables Advanced Query Strategies

About Intertech

Intertech is a Software Development Consulting Firm that provides single and multiple turnkey software development teams, available on your schedule and configured to achieve success as defined by your requirements independently or in co-development with your team. Intertech teams combine proven full-stack, DevOps, Agile-experienced lead consultants with Delivery Management, User Experience, Software Development, and QA experts in Business Process Automation (BPA), Microservices, Client- and Server-Side Web Frameworks of multiple technologies, Custom Portal and Dashboard development, Cloud Integration and Migration (Azure and AWS), and so much more. Each Intertech employee leads with the soft skills necessary to explain complex concepts to stakeholders and team members alike and makes your business more efficient, your data more valuable, and your team better. In addition, Intertech is a trusted partner of more than 4000 satisfied customers and has a 99.70% “would recommend” rating.

+651.288.7000

info@intertech.com

1575 Thomas Center Drive, Eagan, MN 55122

Request A Quote

Introduction to SQL Server Temporal Tables