Tim Mitchell
Follow Tim Mitchell on Twitter  Like Tim Mitchell on Facebook  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter

SQL Server Temporal Tables

SQL Server temporal tablesWhen SQL Server 2016 was introduced, Microsoft added support for temporal tables. SQL Server temporal tables (also referred to as system-versioned tables), which follow the guidelines defined in the ANSI SQL:2011 specification. Temporal tables represent a time-based row versioning system for persisting the full history of changes.

Temporal tables in SQL Server

Keeping track of the history of changes in a table is a common requirement. For data auditing needs, temporal tables can be effectively used to avoid a lot of manual coding. Temporal tables are also useful for building slowly changing dimensions. In the latter role, temporal tables eliminate the cumbersome expire-and-replace logic common in ETL processes for SCDs.

The implementation of temporal tables in SQL Server uses two physical tables for storage. Each temporal table definition has a primary table for CRUD operations. Under the hood, there is a corresponding history table with the same metadata which stores the change history. While the history table can be queried directly, the data within it cannot be modified. This restriction preserves the integrity of the temporal data it stores.

Temporal table resources

Below I have cataloged some of my favorite resources on temporal tables. Some of them are my own, and others link to outside content.

Introduction to SQL Server Temporal Tables. In this introduction, I walk through the essentials of temporal tables, showing an example of how to create a simple temporal table solution.

Using Temporal Tables For Slowly Changing Dimensions. In this post, I shared my thoughts about using temporal tables to substitute for conventional slowly changing dimension tables.

Converting a History Table into a System-Versioned Temporal Table. In this excellent article, Mala Mahadevan walks through the details of converting an existing history table to a temporal table.

Conclusion

While temporal tables may not be the silver bullet fix for every version history problem, they are both useful and simple to use. If you are using SQL Server 2016 or newer and have a need to retain the history of data, I encourage you to consider using these system-versioned tables.