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.

Temporal Tables – Notes and Gotchas
Published: Jun 26, 2022
Here we’ll go through a few of the quirks of Temporal Tables, both things that I’ve been asked most frequently, and also some of the known considerations when looking to use these in your environments.
Most of these are minor and won’t cause you any issues, but there may be the odd one which means you can’t use them at all in your setup. Either way they’re good to know.

Firstly I’ll cover the main gotchas of note:

  • The table must have a Primary Key

  • History tables are PAGE compression as default (this may be bad if you have high CPU already)

  • History and Current tables must reside in the same database

  • History tables cannot have constraints (Primary Keys etc)

  • You cannot Truncate a Temporal Table (if used for audit, that’s a positive)

  • You cannot modify data in the History table (also good if used for audit purposes)

There aren’t actually that many issues really and I don’t consider many, if any, to be actual problems. The only ones that you really need to consider are if you have very high CPU already and are going to be writing to and querying from the History tables frequently as the Page compression may be too much overhead to handle, and also the fact you cannot modify Historical data. Obviously that’s a good thing if you want an audit but I’ve been in many a firm in which historical data needs to be regularly corrected based on corrected feeds and amended source data. In these cases Temporal Tables would be inappropriate. (They could still be used… I’ll show how in a later post, but I’d advise against it)

As for the notes, I’ll do these in the form of question and answer:

Can we amend the schema of a Temporal Table? Yes:

select * from DimCompany
select * from HistoryDimCompany

alter table DimCompany add addressID int

* from DimCompany
select * from HistoryDimCompany

As you can see, we only need change the schema of the main table and SQL Server takes care of the History table for us.

Can we amend the schema of the History table? No:

alter table HistoryDimCompany add ownerID int

Can I index the two tables separately? Yes:

create index cix_DimCompany on DimCompany(companyName)
create index cix_HistoryDimCompany on HistoryDimCompany(validFrom)

This can be a really useful feature for if you want to query the 2 tables in a different manner.

As you can see… Temporal Tables are pretty flexible, easy to work with, and have little drawbacks. In a modern environment I would definitely consider giving them a go to see if they can help with your audits / histories / slowly changing dimension environments.

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.