Stephen Planck covers a feature in SQL Server:
Have you ever wanted to see exactly how a row looked in your SQL Server database a week or a month ago—without writing complicated auditing code or triggers? With temporal tables (also known as system-versioned tables), you can do precisely that. Temporal tables automatically track historical data changes by maintaining multiple versions of each row in a separate history table. Let’s explore how they work, why you might use them, how to set them up, and what best practices to follow.
Click through for a good overview of the feature. I have mixed feelings on the feature because I think it’s halfway-finished and has been since 2016. But there are two things that I think the feature really needs to shine.
The first is user-defined versioning. SQL Server only offers system time for version tracking, meaning it keeps track of when you insert the row into the table. That’s fine for certain historical operations, but terrible if you want to use temporal tables for type-2 slowly changing dimensions, where you often care more about when a record became effective rather than when you inserted the row into the dimension.
The second is efficient historical slicing the same way you can do AS OF operations. AS OF lets you ask questions about what the data looked like at a specific point in time. For warehousing scenarios, we also want to look at the history of changes for a particular keyed record, so you might see all of the changes to a customer or an employee. You can do this with a UNION ALL operation, but that query logic can get complex.
Comments closed