Introduction to SQL Server 2016 Temporal Tables


Temporal table is released with SQL Server 2016.It was introduced first with ANSI SQL 2011 and is now supported in SQL Server 2016.

*Do not mistake Temporal Tables with Temp Table.*


Once you enable Temporal table a system-versioned table gets created which is designed to keep a full history  of data changes and allow easy point in time analysis.

Every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.

In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted.

This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table.

Why temporal?
Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution. Use cases for temporal tables include:

  • Auditing all data changes and performing data forensics when necessary
  • Reconstructing state of the data as of any time in the past
  • Calculating trends over time
  • Maintaining a slowly changing dimension for decision support applications
  • Recovering from accidental data changes and application errors

Read more : Temporal Tables

Leave a Reply
