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.

Querying Temporal Tables in SQL Server
Published: Jun 26, 2022
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.

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.


© Copyright 2020 SQLTraining Ltd.