In previous articles of this SQL Server Audit series (see the TOC at the bottom), we discussed the main concept and real-life needs for having a SQL Server Audit solution. After that, we went through the different methods that can be used to audit the SQL Server instances and databases. In this article, we will show how to take advantages of the System-versioned Temporal Tables feature to audit database table DML changes.

Overview

SQL Server System-versioned Temporal Tables, introduced the first time in SQL Server 2016 to replace the Change Data Capture and Change Tracking features, is a new type of change tracking table, in which full history about the changes performed on the table’s data will be stored, and the validation of the recorded rows will be managed completely by the SQL Server Engine. System-versioned Temporal tables can be used to recover inadvertent data changes, to generate historical reports for analysis purposes or simply to review the current state and the full history of the changes that are performed on each row for auditing purposes.

When you configure a database table with System-Version Temporal Table feature, two copies of the table will be created. The main Temporal table will be used to store the current version of the table rows. On the other hand, the History table, linked to the main table, will be created automatically with the same schema as the main Temporal table and will be used to store the changes history for each row from the main table, without being able to perform any change on the History table manually.

Two datetime period columns should be explicitly defined in the System-versioned Temporal table; the Period Start column, denoted as the SysStartTime column, and the Period End column, denoted as the SysEndTime column, where these columns will be filled automatically by the system. For example, if you insert a new row, the SQL Server Engine will set the Period Start column with the current transaction start time based on the system clock, and keep the row opened by setting the Period End time column with the maximum datetime2 value equal to 9999-12-31. When a change is performed on the inserted row, such as updating any column value or completely delete that row from the table, the SQL Server Engine will write that row with the old values in the history table and close the row by setting the Period End time column with the current transaction start time based on the system clock. For more information about Temporal Tables, check How to track the history of data changes using SQL Server 2016 System-Versioned Temporal Tables.

Create System-versioned Temporal Table

System-Versioned Temporal Tables can be enabled during the table creation process using the CREATE TABLE statement or on an existing table using the ALTER DATABASE statement. To be able to enable System-Versioned in a table, the table should contain a primary key, two not-nullable datetime2 period columns that are defined as GENERATED ALWAYS AS ROW START or END, and passed as parameters in the PERIOD FOR SYSTEM_TIME during the table creation process.

The following CREATE TABLE T-SQL statement can be used to create a new main Temporal table, with the EMP_ID column as the Primary Key, two period columns that are filled automatically by the SQL Server Engine, and finally create a history table with the same schema as the main Temporal table then enable the SYSTEM_VERSIONING feature, as shown below:

To check if the main Temporal table and the associated history table are created successfully, expand the Tables node under your database, and you will see a special icon with a small clock beside the main Temporal table with System-Versioned description between the parentheses. The history table can be viewed under the main Temporal table with History description between parentheses. In addition, a clustered index will be created automatically in the history table involving the two Period Start and Period End columns, as shown below:

Audit Changes Using System-versioned Temporal Table

In order to show how we can use the System-Versioned Temporal Table in auditing the different changes that are performed on a database table, we will fill the created main Temporal Table with few records, using the INSERT INTO T-SQL statement below:

After executing the INSERT INTO T-SQL statement, you can see that the data will be inserted into the main Temporal table, without any data inserted into the history table. This is due to the fact that the first version of the inserted rows will be inserted into the main Temporal table, and these rows will be marked as open, by setting the start time as the start time of the transaction and the end time as the maximum value of the DateTime2 data type, as shown clearly below:

Audit UPDATE Operations

Assume that you are requested to perform a number of changes on the existing table’s data, using the UPDATE T-SQL statement below:

Checking the changes performed on the main and history Temporal table after executing the UPDATE statement, you will see that a copy of the rows before performing the changes will be written to the history table, and marked as close by setting the end time as the UPDATE statement execution time, as shown below:

It is clear from the previous result, that the System-Versioned Temporal Table feature can be easily used to track and audit the changes that are performed on the tracked main Temporal Table, where it will keep all versions of each row. In addition, it can be used to recover from inadvertent changes by providing us with the values before the change. For example, we mistakenly update the phone number of the employee with Emp_ID value equal to 5 with a name rather than a number. To fix that error, we can get the previous value from the history table and revert the update again as shown below:

If you check the main and history Temporal tables again, you will see that a new record will be written to the history table, that contains the second version of the modified row, that is closed with the new update statement time as the period End time, in addition to the previous version of that row, as shown below:

But what if I don’t need my manager or the security team to see that I have mistakenly performed the corrected update. Let us try to delete that record from the history table, using the DELETE statement below:

You will see that it will not allow us to delete the history data that is stored in the history table. This limitation makes the System-Versioned Temporal Table feature a robust SQL Server Audit solution.

Audit DELETE Operations

System-Versioned Temporal Table can be also used to track the DELETE operations that are performed on the main Temporal table. If we run the below DELETE T-SQL statement to delete the employee with Emp_ID value equal to 3 from the main temporal table:

Checking the data from the main and history Temporal tables, you will see that the record will be deleted completely from the main Temporal table but it will be written before being deleted to the history table and marked as closed by setting the end time as the time of the DELETE statement execution, as shown below:

FOR SYSTEM_TIME

SQL Server 2016 introduces a new clause FOR SYSTEM_TIME  to the SELECT statement that helps in querying historical data from the main and history Temporal table, that can be used with five sub-clauses to specify the required period of time. For example, the FROM…TO clause that will return all active rows within the provided time range, regardless of the action starting time. This is achieved by performing a union between the main temporal table and the history table, as shown below:

And the retrieved result will show a list of all versions for each row, including all modification performed on these rows, as shown below:

Limitations

You can see from the previous SQL Server audit result that the System-Versioned Temporal Table feature can be easily used to track and audit changes performed on the table’s data. But these results contain no information about the type of these changes. In addition, the historical data and the main data will be stored in the same database, which makes it less secure auditing solution. On the other hand, no option in this feature to audit the DDL changes or the changes that are performed at the SQL Server instance level.

In the next article of this SQL Server audit series, we will show how to audit the SQL Server using 3^rd party options.

Table of contents

• Author
• Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Latest posts by Ahmad Yaseen (see all)

• Azure Data Factory Interview Questions and Answers - February 11, 2021
• How to monitor Azure Data Factory - January 15, 2021
• Using Source Control in Azure Data Factory - January 12, 2021