Continuing from the previous post, which was a brief introduction to Temporal Tables, we’ll now move on to creating one and seeing what special syntax is involved in that process.
To be honest this tends to look a little confusing when you first see it, but it very quickly makes sense and you can start using it right away.
Firstly, a quick note that there is one specific criteria you need to have and that’s a Primary Key. This generally shouldn’t be a problem as we should all be using them anyway, but I thought it was worth a mention because otherwise SQL Server will throw you an error.
So let’s dive in with a very basic Dimension table to get us started and then we’ll turn that into a Temporal Table.
Basic Dimension:
drop table if exists dbo.DimCompany
go
create table dbo.DimCompany
(
companyID int identity not null primary key clustered,
companyName varchar(100) not null
)
go
So that’s the basis we’re working from.
I’m just going to jump straight ahead to the Temporal Syntax and then explain it below…
drop table if exists dbo.DimCompany
go
create table dbo.DimCompany
(
companyID int identity not null primary key clustered,
companyName varchar(100) not null,
validFrom datetime2 generated always as row start,
validTo datetime2 generated always as row end,
period for system_time(validFrom, validTo)
)
with (system_versioning = on)
go
That’s it… you now have a Temporal Table which you can easily see in SSMS as it has a different symbol to other tables and is clearly marked:
In this example SQL Server has created our History table for us:
This isn’t the most user friendly name, as you can see. But we do have the option of specifying ourselves if we wish to make things clearer:
alter table DimCompany set (system_versioning = off)
drop table if exists dbo.DimCompany
drop table if exists dbo.MSSQL_TemporalHistoryFor_1877581727
go
create table dbo.DimCompany
(
companyID int identity not null primary key clustered,
companyName varchar(100) not null,
validFrom datetime2 generated always as row start,
validTo datetime2 generated always as row end,
period for system_time(validFrom, validTo)
)
with (system_versioning = on (history_table = dbo.HistoryDimCompany))
go
(Note that we need to turn System Versioning off before we are allowed to drop the table. Also that we need to drop both the table and history table separately)
Now, once created we can use the table in the same way as any other EXCEPT you must remember to use a Column List as you’ll not be inserting directly into the validFrom and validTo columns… these are done in the background by SQL Server itself:
insert into DimCompany(companyName)
select 'Pear Computers'
go
select *
from DimCompany
There you have it, you’ve created your first Temporal Table.