Introduction to Temporal Tables

Microsoft has recently released SQL Server 2016 as Community Technology Preview 2 (CTP2) and utilizes temporal tables to simplify the process of auditing records and restoring tables by retrieving its temporal history data. Creating new temporal table schemas and scaling existing database schemas to the new system-versioned architecture prove to optimize processing speeds and add an extra layer of security to tables in the database.

System-Versioned TablesA temporal table, also referred to as a system-versioned table, retains the past values of each record so the information can be queried at any point in time from a corresponding history table, rather than solely the current value of each record as it is updated. So for any given record there is an enumerated current version in its current table and zero or more prior versions in the temporal history table. When an assertion is instantiated by UPDATE or DELETE actions at the specific time the action was executed, the record’s current version is kept in its table while its previous versions are created as rows of data in the temporal table in the database.

Creating a new temporal table is simple. It requires enabling system-versioning for the table, declaring not-null datetime columns to retain the historical data as its records are updated, tying the current table to the history table with primary key constraints, and instantiation of all data columns for the history table to retain the data to be queried. Working with similar, non-temporal, schemas that are triggered by events can be easily converted to temporal tables by executing ALTER TABLE, hiding existing columns that do not need to be updated, and updating schemas to match the system-versioning style by removing triggers/change data capture processes. SQL Server’s data consistency check will run to ensure that there are no overlapping schemas or gaps. The new temporal table will have the same set of columns as the current table, but with all constraints removed and its given its own set of indexes and statistics so that it can retain its validity. This allows the user to query the temporal history table with specific temporal queries, shortening the query time and simplifying the entire process of extracting historical data. SQL Server 2016 supports indexing strategies for implementation of clustered/non-clustered rowstores to optimize both storage size and database performance.

Integration of system-versioning tables greatly simplifies querying historical data and serves as a more secure process to save current version data while retaining the integrity of historical data as it is updated, improving data management capabilities. Permissions required for the creation and altering of temporal history table schemas add an extra layer of security, but the temporal history table itself, while it’s not read-only, cannot be modified directly regardless of permissions granted to the user. Dropping and altering columns, deleting rows of data, and dropping the table are all disallowed, and the database audit log shows all attempts to create or alter a temporal history table. This functionality retains its integrity as a secure and reliable history table and proves useful for auditing records.

For software performance optimization, temporal tables aid in the reduction of database querying time, improves security with its structural modification requisites and the ability to restore corrupted or lost data, and increases the validity historical data to be audited as it changes over time. At Ayoka Systems, our database design services offer insights of the best practices for optimal database engineering for updates, maintenance, and integration for an array of industries while abiding by industry and governmental regulations.