What is Temporal Table ?

In a traditional table we can keep data along with CRUD operation but can’t view the history of data contained in tables.On the other site, temporal tables allow to automatically keep history of the data in the table A system-versioned table allows you to query updated and deleted data, while a normal table can only return the current data.

Temporal tables are not replace the change data capture (CDC) feature. CDC uses the transaction log to find the changes and typically those changes are kept for a short period of time (depending on your ETL timeframe). Temporal tables store the actual changes in the history table and they are intended to stay there for a much longer time.

Why to use ?

  1. Temporal tables captures the lifetime of a record based on the physical dates the record was removed or updated.
  2. Temporal tables add data auditing features to existing applications or solutions when you need it.
  3. System-versioned temporal tables store values for period columns in UTC time zone, while it is always more convenient to work with local time zone both for filtering data and displaying results.
  4. By that user can view how entire data sets changed over time.
  5. Allow users to transparently keep the full history of changes for later analysis, separately from the current data, with the minimal impact on the main OLTP workload.

TT01

Why it required ?

  • Audit. With temporal tables you can find out what values a specific entity has had over its entire lifetime.
  • Slowly changing dimensions. A system-versioned table exactly behaves like a dimension with type 2 changing behavior for all of its columns.
  • Repair record-level corruptions. Think of it as a sort of back-up mechanism on a single table. Accidentally deleted a record? Retrieve it from the history table and insert it back into the main table.

How it works ?

System-versioning for a table is implemented as a pair of tables, a current table and a history table. Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

  • Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.
  • Period end column: The system records the end time for the row in this column, typically denoted as the SysEndTime column.

The current table contains the current value for each row. The history table contains each previous value for each row, if any, and the start time and end time for the period for which it was valid.

TT02

How do I query temporal data?

The SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

SELECT * FROM Employee FOR SYSTEM_TIME BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'                                            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

 

TT03

Prerequisite for creating temporal Table    

  • A primary key must be defined
  • Two columns must be defined to record the start and end date with a data type of datetime2. If needed, these columns can be hidden using the HIDDEN flag. These columns are called the SYSTEM_TIME period columns.
  • INSTEAD OF triggers are not allowed. AFTER triggers are only allowed on the current table.
  • In-memory OLTP cannot be used in SQL Server 2016. Later on, this limitation has been lifted. Check out the documentation for more information.
  • By default, the history table is page compressed.
  • ON DELETE/UPDATE CASCADE is not permitted on the current table. This limitation has also been lifted in SQL Server 2017.

Limitations Of temporal Table   

  • Temporal and history table cannot be FILETABLE
  • The history table cannot have any constraints
  • INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns
  • Data in the history table cannot be modified