다음을 통해 공유

Temporal Data with System Versioned Tables in SQL Server 2016

  • 아티클

SQL Server 2016 will be coming with Temporal Data support following the ANSI SQL 2011 standard. This will add support for Temporal Tables as a Database feature that will provide a built-in support to provide information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

(With the current release of CTP 2 version you can play with this feature)

 

A Temporal Data is tightly related to a Time period and used to handle the time-varying data. It provides the ability to look at data trends, types of data changes, and the overall data evolution within your database.

 

 

A Temporal Table is a table for which a PERIOD definition exists and which contains system columns with a datatype of datetime2 into which the period of validity is recorded by the system, and which has an associated History Table into which the system records all prior versions of each record with their period of validity.

 

>> To maintain Temporal data it require 2 tables:

1. A parent Temporal or System Versioned Table, created by simple CREATE TABLE statements with few more options.

2. A child History Table, automatically created by the above CREATE TABLE statement and further maintained by the SQL DB Engine for all DML operations.

 

>> DML operations on Temporal Table:

1. The INSERT operation will add a new record in the parent Temporal Table.

2. The UPDATE operation will copy the existing version of the row to the child History Table, and replaces it with new values in the parent Temporal Table.

3. The DELETE operation will copy the existing version of the row to the child History Table, and deleted the existing row in the parent Temporal Table.

4. While reading (SELECT) the rows you've to specify the datetime value and depending on it, it will check the parent Temporal or child History table to fetch the data for you.

 

>> With Temporal Data support you can do:

1. Time Travel

2. Data Audit

3. maintain Slowly Changing Dimension (SCD)

4. recover from accidental data changes by repairing record-level corruptions (for DBAs).

 

>> I've talked on working with various option on Temporal Data/Tables with examples on my personal blog below:

1. CREATE a new set of Temporal Tables .

2. Enable Temporal or System Versioning on existing tables with data.

3. View the in-depth Demo.

 

>> More on Temporal Data/Tables on MSDN BoL: https://msdn.microsoft.com/en-us/library/dn935015.aspx

Comments

  • Anonymous
    July 04, 2015
    Any quick ideas how this could be easily extended to include bi-temporal queries over the transaction ValidTime vs. the SystemDerived transaction time captured by the temporal DDL.

  • Anonymous
    July 13, 2015
    @Simon, right now with CTP2 update Bi-Temporal is not supported. It only supports Transaction-Time i.e. historical till the current time period. Not sure if future Valid-Time will be supported or not in this release.