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.