Converting an Existing SQL Server Temporal Table

In my previous article on SQL Server Temporal Tables I showed how to create a Temporal Table in a SQL Server database and do time-based querying but what if you have an existing table loaded with data? Sure, creating a new table and migrating it in can work but an easier solution is to just add the history table and system versioning to an existing table.

We will start with our example table, it’s a fairly simple for simplicity but I have made the conversion on much larger and complex tables.

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),
);

And with a little sample data we are ready to begin.

SELECT * FROM [ApplicationUser]

There are now a couple of things we need to convert; we need a column to represent the valid start date and a valid end date. I tend to call these ValidFrom and ValidTo but SQL doesn’t care. These columns need to be of the type DATETIME2, be decoracted with GENERATED ALWAYS AS ROW START/END. They also need to be populated with default values when we alter the table. When we have these columns set, we just need to set them as the period for system time.

For our example table an alter script looks like this:

ALTER TABLE [dbo].[ApplicationUser] ADD
[ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT SYSUTCDATETIME(),
[ValidTo] datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
PERIOD FOR SYSTEM_TIME ([ValidFrom],[ValidTo]);

Now our table meets the requirements and the final step is turning on system versioning.

ALTER TABLE [dbo].[ApplicationUser]
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ApplicationUserHistory]));
GO

Now moving forward all the rows will get history for adds, updates, and deletes. What if you already have history data? Let’s suppose you were already tracking data or could transform what you have stored into this format, can you convert it to a temporal table? You certainly can, let’s go back to our original example table and let’s say we had a history table.

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),
);
GO

CREATE TABLE [dbo].[ApplicationUserHistory]
(
	[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		NOT NULL,
	[ValidTo]			datetime2		NOT NULL,
);
GO

Two very simple tables, and we’ll throw a little bit of sample data just to prove it out.

INSERT INTO ApplicationUser(UserId, FirstName, LastName, JobTitle, LastUpdatedBy) VALUES('U001','John','Doe','Supervisor','Frank')
INSERT INTO ApplicationUser(UserId, FirstName, LastName, JobTitle, LastUpdatedBy) VALUES('U002','Michael','Scott','Technician','John')
INSERT INTO ApplicationUser(UserId, FirstName, LastName, JobTitle, LastUpdatedBy) VALUES('U003','Betty','White','HR','Mary')
INSERT INTO ApplicationUser(UserId, FirstName, LastName, JobTitle, LastUpdatedBy) VALUES('U004','Mary','Evans','Supervisor','Michael')
INSERT INTO ApplicationUser(UserId, FirstName, LastName, JobTitle, LastUpdatedBy) VALUES('U005','James','Smith','Technician','Betty')

INSERT INTO [ApplicationUserHistory](UserId, FirstName, LastName, JobTitle, LastUpdatedBy, ValidFrom, ValidTo) VALUES('U001','John','Doe','Supervisor','Frank', '2022-11-01 23:13:01.8318438', '2022-11-02 23:13:01.8318438')
INSERT INTO [ApplicationUserHistory](UserId, FirstName, LastName, JobTitle, LastUpdatedBy, ValidFrom, ValidTo) VALUES('U002','Michael','Scott','Technician','John', '2022-11-01 22:13:01.8318438', '2022-11-02 23:13:01.8318438')
INSERT INTO [ApplicationUserHistory](UserId, FirstName, LastName, JobTitle, LastUpdatedBy, ValidFrom, ValidTo) VALUES('U003','Betty','White','HR','Mary', '2022-11-01 12:13:01.8318438', '2022-11-02 23:13:01.8318438')
INSERT INTO [ApplicationUserHistory](UserId, FirstName, LastName, JobTitle, LastUpdatedBy, ValidFrom, ValidTo) VALUES('U004','Mary','Evans','Supervisor','Michael', '2022-11-01 14:13:01.8318438', '2022-11-02 23:13:01.8318438')
INSERT INTO [ApplicationUserHistory](UserId, FirstName, LastName, JobTitle, LastUpdatedBy, ValidFrom, ValidTo) VALUES('U005','James','Smith','Technician','Betty', '2022-11-01 10:13:01.8318438', '2022-11-02 23:13:01.8318438')

Now I realize my history table is exactly like the generated history table and that a database already doing this type of track is unlikely to exactly match like this. My point here is to create an easily reproducible way of proving out the idea. At this point we need to add the same period columns to our non-history table, and then link it to the history table.

ALTER TABLE [dbo].[ApplicationUser] ADD
[ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT SYSUTCDATETIME(),
[ValidTo] datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
PERIOD FOR SYSTEM_TIME ([ValidFrom],[ValidTo]);

Now our table meets the requirements and the final step is turning on system versioning.

ALTER TABLE [dbo].[ApplicationUser]
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ApplicationUserHistory]));
GO

You may note that script is exactly the same as the previous example. This means that if you set system versioning on and the history table referenced doesn’t exist SQL Server will do the handy job of making it for you, but if it already exists and meets the requirements necessary will use the existing table.

Conclusion

You are now equipped to use temporal tables on new or existing tables and have a trick up your sleeve if you are asked to add change history to a table of data.

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

Converting an Existing SQL Server Temporal Table