Creating a Temporal Table in SQL Server
Published: Aug 24, 2019
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron