Querying Temporal Tables in SQL Server
Published: Dec 05, 2021
Again, following on from my last post, we’ll be looking at Temporal Tables a little more. Last time we simply created one and entered a row of data whereas this time we’ll be looking to query them a little more and see what syntax is required to do so.
This, again, isn’t too complicated when you know the quirks and the syntax isn’t hard once you’ve used it a couple of times.

Let’s make sure that we’re all set:

-- Use this if you already have DimCompany as Temporal, otherwise comment it out
alter table DimCompany set (system_versioning = off)

drop table if exists dbo.DimCompany
drop table if exists dbo.MSSQL_TemporalHistoryFor_1877581727

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

insert into DimCompany(companyName)
select 'Pear Computers'

select *
from DimCompany

Okay… so what happens if we now update this record by changing the company name?

select *
from DimCompany

update DimCompany
set companyName = 'Pear Technology Ltd'

select *
from DimCompany

On the face of it, you’d never know there were Temporal Tables involved… but let’s look more closely…

select *
from DimCompany

select *
from HistoryDimCompany

So our History table is working, which is nice. But aren’t we still in the situation in which we need to write clumsy UNION ALL style queries to get our data back for point in time? Nope.

select *
from DimCompany
for system_time all

select *
from DimCompany
for system_time as of '2019-03-07 07:15:00'

As you can see, there is some different syntax involved here, but nothing to scary or complicated at all… and yet it allows us to query point in time with none of the previously expected complexity or older methods.

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

Kutech  CEO
Kevin  Urquhart


I am a SQL Server DBA, Architect, Developer, Trainer, and the owner and CEO of Kutech. This blog has been going for nearly 10 years now over several guises and this is its new home. I hope you find it useful whatever your endeavour.


© Copyright 2021 Kutech Solutions Ltd.

Our site uses cookies only to personalise content and analyse traffic. We do not use cookies for advertising. Policy