Episode 106
| July 25, 2017
| 53 minutes
Temporal Tables
User: What was that record before it was updated?
Me: I don’t know. Maybe I could restore a backup?
User: Really? You have to do all that? It is just one record.
Me: (under my breath) Yes, a record YOU updated.
If you have ever had a conversation like this you know how difficult it can be to implement auditing of records as they change. With temporal tables, a new feature, we have the ability to track point in time information about a record without the huge expense of setting up auditing and tracking. While you won’t want to use this on every table, when there are tables with sensitive data you want to audit from time to time, this feature will come in handy. We are happy to have Randolph back on the program with us to talk about this new feature.
Episode Quotes
“That ability to kind of see what was there before it was updated, that seems to be the focus of temporal tables.”
“It’s all effective dated, so every row on a temporal table has to have a start and an end timestamp.”
“You don’t know how much space it’s going to use until you start doing some actual real life testing… That’s the biggest limitation for me.”
“When you create a temporal table the history table only takes the clustering index, so mainly the primary and the structure of the table.”
Listen to Learn
03:28 Temporal Tables
05:44 Auditing relative to user tracking
08:20 What are other reasons that people putting in temporal tables?
09:49 Start time and end time parameters for temporal tables
11:39 Using system_time in querying
12:50 Recovering data
13:35 Flushing the history table
14:49 Temporal Table in Management Studio
16:17 History table and clustered index
18:01 Modifying/Altering Temporal Tables
21:17 Data consistency check dealing with corruption
22:44 Scenarios where SYSTEM_VERSIONING = ON
27:50 Other limitations of temporal table
28:39 Is system time applicable when querying joined temporal tables?
31:06 When should you use temporal tables?
32:23 Tips on current feeding side of temporal tables
43:52 SQL Family questions
Our Guest
Randolph West
Randolph West solves technology problems with a focus on SQL Server and C#. He is a Microsoft Data Platform MVP who has worked with SQL Server since the late 1990s. When not consulting, he can be seen acting on the stage and screen or doing voices for independent video games.
Meet the Hosts
Carlos Chacon
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene Meidinger
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin Feasel
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.
Want to Submit Some Feedback?
Did we miss something or not quite get it right? Want to be a guest or suggest a guest/topic for the podcast?