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
select * 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.