The use of Temporal Tables

There are many reasons to use Temporal Tables. One is to keep track of changes made to your data. Or it could be that you want to be able restore your data to how it was before the changes were made. Or it could be for reporting purposes: what did your data look like at a certain point in time.

For all these reasons, Temporal Tables could be used. They are available since SQL 2016. One of the benefits of Temporal Tables is that they are automatically protected by SQL Server. That means you cannot run insert, update or delete statements against the History Table. Nor can you do a truncate statement.

So Temporal Tables are great to keep track of historical changes. But be sure not to use them on every table. Every change will trigger that a complete record is copied into the History Table. So yes, that will generate a lot of data and it will use disk space. Especially for tables with lots of updates.

So, unless you have unlimmited disk space, use Temporal Tables whenever keeping track of changes really matters. Be careful with tables that have lots of updates.

Ok, with that said, let's create a Temporal Table.

How to create a Temporal Table

Creating a Temporal Table looks a lot like a regular table with some exceptions. But you will need to add two datetime2 columns for the period that SQL Server sees it as the current record. Also you will need to turn System Versioning on.

create table Products (
  ProductID uniqueidentifier not null
    default newid() Primary Key nonclustered,
  Code nvarchar(30),
  Description nvarchar(150),
  Active tinyint default 1,
  Created datetime2,
  CreatedBy uniqueidentifier,
  Modified datetime2,
  ModifiedBy uniqueidentifier,
  SysStart datetime2 generated always as row start hidden not null,
  SysEnd datetime2 generated always as row end hidden not null,
  period for system_time(SysStart, SysEnd)
  )
with
(
  system_versioning = on (history_table = dbo.ProductsHistory)
)

Here I created 2 columns (SysStart and SysEnd) that are to be used for the period that the record is/was the current record. Since they are of no real use in the Products Table itself, I've set them to hidden.

I also turned System Versioning on. By default SQL Server will come up with a name referencing the ID of the Table in Sysobjects. But since that is hard to read, I specified the name for the History Table explicitly. If you run the query and look in the Object Explorer of SQL Management Studio you will see this:

Do note that the ProductsHistory Table looks identical to the Products Table. But the Primary Key on ProductID is not there. That is because any change will be saved to this Table so there will be multiple rows for the same ProductID.

Let's insert some records

Next let's put some data into our Products Table. Just 2 records for tasting purposes:

declare @PersonID uniqueidentifier
set @PersonID = newid()

insert into Products
(Code, Description, Created, CreatedBy, Modified, ModifiedBy)
values
('PRD01', 'Product 01', getdate(), @PersonID, getdate(), @PersonID),
('PRD02', 'Product 02', getdate(), @PersonID, getdate(), @PersonID)
GO

And now let's check what we have:

select * from Products
select * from ProductsHistory
GO

This will show that the records were inserted into the Products Table. But nothing is yet inserted into the ProductsHistory Table. Also note that you will not see SysStart and SysEnd in the Products Table since these were set to hidden. You will see them however on the ProductsHistory table.

Now let's run an update

Next we will do an update of the Products Table and set one Product to inactive.

update Products
set Active = 0
where Code = 'PRD01'
GO

And now let's check what we have using the same:

select * from Products
select * from ProductsHistory
GO

The Products Table will still have 2 records. But now there is 1 record in the ProductsHistory Table too. Which is an exact copy of the record before it was updated.

Select from Versioned Tables

Ok, so you can use the ProductsHistory Table just like any other Table. But it is read-only: no insert, update or delete statements allowed and no truncating the table. You can use the select statement anyway you like, so you can dive into the history anyway you want.

But what's more interesting is the Products Table. From the results, copy the SysEnd from the ProductsHistory Table and use it for this query:

select *
from Products
for system_time as of '2021-02-22 21:23:58.7174097'
GO

Nothing special there, huh? Just the records from the Products Table as expected, right?

You are absolutely right. But now let's change that datetime to just before the update. By (in this case) changing the 7 at the end to 6:

select *
from Products
for system_time as of '2021-02-22 21:23:58.7174096'
GO

Hey! This gives you the results as you would have seen at that point in time! Let's put the date back one day and see what that gives:

select *
from Products
for system_time as of '2021-02-21 21:23:58.7174096'
GO

This will give no results, even though there are records in the Products Table. But since we just inserted them today, they were not there yesterday!

If only you had knew!

Ah, if only you had known this before! Well, you cannot get the change history from before today. But you can change any existing table to be a Temporal Table as from now. Here's how to do that:

Define (if not already present) a primary key on the table
Add start and end period columns to the table
Update row effective from and to period values for existing rows
Alter column to add not null constraint
Declare system period columns
Enable system versioning on the table

Except for the Primary Key, which you can easily change in the Management Studio, this script will handle it all. Just change the name of the Table from this example to the Table you want to be Versioned.

alter table dbo.Products
add SysStart datetime2
GO

alter table dbo.Products
add SysEnd datetime2
GO

update dbo.Products
set
SysStart = '19000101 00:00:00.0000000',
SysEnd = '99991231 23:59:59.9999999'
GO

alter table dbo.Products
alter column SysStart datetime2 NOT NULL
GO

alter table dbo.Products
alter column SysEnd datetime2 NOT NULL
GO

alter table dbo.Products
add period for system_time (StartTime, EndTime)
GO

alter table dbo.Products
set(
system_versioning = on (history_table = dbo.ProductsHistory)
)
GO

But how do I stop this?

As long as System Versioning is on, you cannot delete anything from the History Table or truncate it. Also, you cannot drop the History Table since the history would be lost.

So if you don't need versioning anymore, you need to turn it off before you can do anything to the History Table. So the clear up the tables from this example, you would need this:

alter table Products set (system_versioning = off)
drop table Products
drop table dbo.ProductsHistory
GO

That's it for today!

Let's do a quick recap. Temporal Tables are a great way to keep track of changes in your Tables. Each change is stored in a History Table so it will take some disk space, especially for Tables with lots of updates. So consider if the benefits of logging the changes is worth the use of disk space.

Use the "for system_time as of" in your select statement to go back to a specific point in time. Or just query the History Table to get the information you need.

While System Versioning is on, you can only use select statements on the History Table. It is read-only to protect data consistency. You will have to set Versioning off before you can clean up the History Table.

Let me know if and how you use this in your projects and stay tuned for more Tips & Tricks.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!