Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Creating a Temporal Table in SQL Server
Published: Dec 02, 2020
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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.