×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Data Audits in SQL Azure with Temporal Tables
 

Perform Data Audits in SQL Azure Using Temporal Tables

Tuesday Sep 13th 2016 by Sandeep Chanda

Learn why Temporal Tables are a powerful feature to help you tailor your auditing needs without having to write any code.

The support for Temporal Tables has now been extended to SQL Azure databases. With Temporal Tables, you can track changes made to your data and store the entire history either for analysis or for making the information actionable. For example, a compensation logic can trigger based on historical changes resulting from an exception scenario. The important aspect about Temporal Tables is that it can keep data stored over a timeline. Data in context of time can be leveraged in reporting facts valid for that specific period of time. It then becomes very easy for you to gain insights from data as it evolves over a specified period.

Auditing is probably the most significant use case for Temporal tables. Temporal tables are created by enabling system versioning on new or existing tables. The following SQL script creates a table that is temporal system-versioned:

Note that in addition to the regular fields for the Person entity, there are three additional columns. The ValidFrom and ValidTo fields allow time-based tracking of any information updates on the Person table and the WITH statement allows enabling the historical tracking of changes in the PersonHistory table.

Create a Person record using the following statement-

Run multiple updates on the table to modify the address field. If you query the history table, you will see records for all the updates made:

You can enable system-versioning on existing tables by altering the schema and introducing the ValidFrom and ValidTo columns. The history table becomes the focal point for your auditing needs without requiring you to write any programming logic for the purpose. It then also becomes very easy to perform Point-in-Time analysis such as tracking trends or differences between two points in time of interest. The other popular use case that Temporal Tables enable you to perform is anomaly detection. For example, it can help you figure out a miss in your sales forecast. Temporal Tables are a powerful feature helping you tailor your auditing needs without having to write any code!

Home
Mobile Site | Full Site
Copyright 2018 © QuinStreet Inc. All Rights Reserved