Keep full history of every values in each table

Posted on

Question :

How would you do to keep full history of every values of all tables in a DB, which can be queried with a specific date (for each and every column of all tables)? I mean: one would just need to specify a date for which the value was “active” or in “validity”.

In a dumb way, I would add to each table a “validity start date” column. And instead of copying every value, I would only copy values that changed, ad set all the other columns to NULL. However, this solution is really horrible, since it complexifies the queries, even if it takes less space.

Is there a specific technology (I am in the Microsoft world) that enables to do that, or a specific way to model the database to do that (except creating a table for each column of each table)?

I would like to mention that I am not a DBA but a dev. KR!

Answer :

SQL Server has a feature, introduced in SQL Server 2016, called Temporal Tables. Using system-versioned temporal tables is essentially an out-of-the-box alternative to using triggers or application logic to maintain historical record of edits to a table.

Temporal tables do come with a number of limitations and restrictions, which may require alternative options (such as hand-crafted triggers & history tables), depending on your use case. You would need to review your full set of requirements, compared against the set of limitations.

In addition to the great recommendations from AMtwo, you may also find the following other SQL Server features useful for your goals as well:

  1. Change Data Capture – “Change data capture (CDC) records insert, update, and delete activity that applies to a SQL Server table. This makes the details of the changes available in an easily consumed relational format.

  2. CHANGETABLE – “Returns change tracking information for a table. You can use this statement to return all changes for a table or change tracking information for a specific row.

Bonus: Change Tracking – This only gets you high level information about what rows have changed, but not necessarily a log of the details: “Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications…Applications can use change tracking to answer the following questions about the changes that have been made to a user table: What rows have changed for a user table? Has a row changed?

You might also find this article on auditing SQL Server changes useful.

Leave a Reply

Your email address will not be published. Required fields are marked *