I first heard about Temporal Tables when it was mentioned in a Channel 9 video with Scott Klein and Borko Novakovic early last year. To be honest I was a little surprised it wasn’t already in place but after finding out more I was annoyed it wasn’t available until SQL Server 2016. Ah well.
In short, you flip a switch on a table to make it a temporal and you can access it at any point in time. No messing around with triggers or updating your stored procedures.
Born SQL did a great 4-part series last year which are all worth a read
- An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean
- Temporal Tables Under The Covers
- Modifying Temporal Tables – A Primer
- Temporal Tables – When To Use Them
The only part I wish were possible was to make it easy to push changes into the future, but that could get messy. This also touches on one of my main gripes when describing these kind of features and that’s relating them to time travel. Maybe it’s just me but I always think of time travel as a way to travel into the future as well as the past. Audit logs or history tables don’t really work like that. Perhaps a better way might be to think of each history table as a stack of incredibly tedious and repetitive journals?
However as SQL Server 2016 isn’t available in any of the projects it’s just something I’ll keep an eye on and get a better understanding of it. Until then I’ll be stuck with doing it my stored procedures.