Temporal Tables – Notes and Gotchas
Published: Dec 05, 2021
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.

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