Querying Temporal Tables in SQL Server
Published: Aug 24, 2019
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
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

insert into DimCompany(companyName)
select 'Pear Computers'
go

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.

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