Tracking table data changes using Azure SQL Database temporal tables

One of the requirements of a recent project I was working on, was maintaining history of database table data as part of an audit trail. My first thought about the technical solution was to use triggers, but after some research I learned about temporal tables.

Temporal tables have been introduced in SQL Server 2016 and are also available in Azure SQL databases. They maintain history of their data for a given retention period. The history is managed by the SQL database engine and only requires some configuration when creating the table. All table records will have a time context so point-in-time analysis can be applied.

All queries mentioned in this blog can be found on GitHub.

Creating temporal table

In the following SQL script, a temporal table is created which contains data about persons and their cars.

The most of the SQL statement is pretty common, only the 2 parts of extra configuration are different:

• At lines 5 through 7 the time context for every record is defined. ValidFrom contains the timestamp when the record became current and ValidTill contains the timestamp when the record became obsolete.
• On line 14 we define the table where the historical data is stored with a retention period which is defined at line 15.

The history table will be created automatically, and the primary key defined at line 8 will be used to maintain the relationship of the data in the current table and the history table.

After running this SQL script, in SQL Server Management Studio you can see the current table and history table as child of the that table.
The history table has been created with the same columns as the current table. The only difference is that the primary key of the ID column has been removed. This is done because a record can have multiple changes and this would lead to more then one record in the history table with the same ID value.

Inserting data

In order to create a record in the current table, only the columns that are not automatically generated by the database engine ( ID, ValidFrom & ValidTill) and which are not nullable ( FirstName & Car) require data.

After the inserts, the current table shows the records with in ValidFrom the timestamp of the creation of the record and in ValidTill the timestamp of the record becoming obsolete. Because this is the current table, the ValidTill will here always contain the maximum date value.

The history table is still empty because there have been no changes for these records.

Updating data

Now that the table contains some data, it can be updated using the following SQL script.

The following steps are executed by the database engine as a result of running this SQL script:

1) The record of David is copied from the current table to the history table without changes.

2) The ValidTill of this record of David in the history table is changed into current datetime.

3) The record of David in the current table is updated with ValidFrom as current datetime.

After doing several updates on the record of David (with few seconds in between updates), a more extensive history of the record is created.

Deleting data

The next step is to delete the record of David from the current table.

In this case, the following steps are executed by the database engine:

1) The record of David is copied from the current table to the history table without changes.

2) The ValidTill of this record of David in the history table is changed into current datetime.

3) The record of David in the current table is deleted.

Querying data

Temporal table data can be retrieved using regular SQL query statements and the new FOR SYSTEM_TIME clause. This clause can be used for point-in-time analysis in combination with one of the following filters:

• ALL; returns all records from the current and history table.
• AS OF <datetime>; returns all records that were current at the specified datetime.
• FROM <start_datetime> TO <end_datetime>; returns all records that were current within the specified datetime range.
• BETWEEN <start_datetime> AND <end_datetime>; returns all records that were current within the specified datetime range and on the end datetime.
• CONTAINED IN (<start_datetime>, <end_datetime>); returns all records that were current within the specified datetime range and on the start and end datetime.

The following queries returns all David’s records, even though his records are no longer available in the current table:

To retrieve David’s records within and on a datetime range, the following queries is used:

Adding columns

Columns which are nullable can be easily added to the current table.

The column will automatically be added to the history table.

Columns which are not nullable require some coding which is shown below.

First, the column is added as nullable. Because this is done when versioning is enabled the column is automatically added to the history table. Next, versioning is turned off and the column is filled with default values. Finally, versioning can be re-enabled and the column can be changed to not nullable.

Please note: the ValidFrom timestamp in the current table will be reset to the current datetime because of re-enabling versioning.

Change existing table into temporal table

Existing tables can be changed into temporal tables by first adding the time context columns. These columns need to have default values because they are required and the table will (probably) already have existing data. After the columns have been added, versioning can be enabled in the same way as when creating a new table.

Thank you for reading my story. You can read about Twyzer on https://twyzer.nl (in Dutch) or click here for the English translation.