SQL - Temporal Tables

Temporal is a database feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2016. Temporal in SQL Server is designed to simply the handling of time-varying data. It provides the ability to look at data trends, types of data changes, and the overall data evolution within your database. 

Temporal support is useful in cases when one has to test a forecasting model to see what kind of a decision the model would make, if it had only knowledge about the world as it existed then, vs. decision that would be made know with all the updated and revised facts.

Why Temporal - Temporal is the excellent feature to capture complete audit. Using temporal for a table shared by some other column will transparently add history to all table shards enabling time-travel and data audit scenarios.

1) Real data sources are dynamic

2) Historical data analysis is often keys to business success

SQL Server Temporal design to simplify handling of time varying data and each record in temporal table is actually considers to be valid and true. Temporal database has capability to answer some very interesting questions like how your data look like a year ago or month ago and what was the data evolution in this time frame also.  Now, we can understand why people would use this feature because business users are very interested to know the trends and type of changes and data evolution but traditionally databases are fail to support this kind of scenarios.

SQL Server Temporal design to simplify handling of time varying data

Time Travel stands for adding time travel keep ability to your database means adding time stamp to your application which will be capable to define the data evaluation and provide a visualised feature to the business users to analyses the two different time stamp of the same data to draw some conclusion for the business prospective. This feature provides a clear picture of your database with full data evolution Trends.

How to Start with Temporal

This is very interesting feature, we can use our existing tables for temporal or create new one also and there is no change in DDL, DML and querying on the table as similar we do with our normal tables.

This is very convenient to introducing temporal into our existing table schema but we have new capability to answer some interesting questions on your historical data.

USE DATA2012; GO /* 1. NEW TEMPORAL TABLE WITH PERIOD DEFINATION*/ CREATE TABLE DBO.DEPARTMENT ( DEPTID INT IDENTITY (1,1) NOT NULL, DEPTNAME VARCHAR(30) NOT NULL, CONSTRAINT PK_DEPT PRIMARY KEY CLUSTERED (DEPTID ASC), /*TEMPORAL SPECIFIC - PERIOD DEFINITION */ [VALIDFROM] [DATETIME2] (0) NOT NULL, [VALIDTO] [DATETIME2] (0) NOT NULL, PERIOD FOR SYSTEM_TIME ([VALIDFROM],[VALIDTO]) ) /*TEMPORAL SPECIFIC - SYSTEM-VERSIONING CONFIGURATION*/ WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE_[HISTORY].[DEPARTMENTHISTORY])) USE DATA2012; GO /* 2. EXTEND TABLE WITH PERIOD DEFINATION*/ ALTER TABLE DBO.DEPARTMENT ADD /*TEMPORAL SPECIFIC - PERIOD DEFINITION */ [VALIDFROM] [DATETIME2] (0) GENERATED ALWAYS AS ROW START CONSTRAINT D_VALIDFROM DEFAULT '2015.09.13', [VALIDTO] [DATETIME2] (0) GENERATED ALWAYS AS ROW END CONSTRAINT D_VALIDTO DEFAULT '9999.12.31 23:59:59',, PERIOD FOR SYSTEM_TIME ([VALIDFROM],[VALIDTO]) GO /*TEMPORAL SPECIFIC - DEFAULT HISTORY CONFIGURATION*/ ALTER TABLE  DBO.DEPARTMENT WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE_[HISTORY].[DEPARTMENTHISTORY]))

This new feature is known as System-Time Works and system consists of two tables such as primary one (Temporal Table) and secondary table (History Table). In this way, we can say that application never talked with the history table and always interacts with the primary table means whenever application insert a new record into the temporal table then no record will insert into the history table but whenever application user will update the temporal data table then old version of the record will go into the history table from the temporal table and keep the time stamp of operation has happen.

Whenever business user ask for the history of the record then output result fetch the information from temporal table including data from the history table with the help of time stamp. With a temporal table, the value of each record at any point in time can be determined, rather than just the current value of each record. A temporal table is also referred to as a system-versioned table.

SQL Server is using a separate table as history table and yes! You can provide an existing history table. There are some basic conditions that would need to be met, for example: the schema of the history table in terms of number of columns, their types and null ability would need to match the actual (current) data table. The system will do consistency checks to make sure this is the case, when you turn the temporal feature (system versioning) on. Overall, there's a good level of flexibility when it comes to managing the history table to best suit your use cases. For example, on a history table you can, independently from the actual table, setup compression, indexes and partitioning, to name a few options.

In general, temporal gives you all flexibility to optimize the system for your target scenarios since current and history tables can be managed and configured independently. You can do that later on even if you let system to create history table for you.

For large temporal tables, we strongly recommend that you use partitioning based on period column (SysEndTime) because not only that you will speed up DMLs and temporal querying but you also will be able to support data aging very efficiently.

Data Audit - However, temporal can work together with SQL Audit in two scenarios:

• Whenever we want to track that who is changing temporal configuration (SYSTEM_VERSIONING ON/OFF) you can use SQL Audit to track ALTER TABLE event. We have extended DDL events to emit temporal related information.
• When we want to correlate the SQL Audit and temporal to get more information such as how the data looked like before and after certain user operations.

USE DATA2012; GO /* DATE AUDIT WITH TEMPORAL TABLE */ SELECT DEPTID, DEPTNAME,  VALIDFROM,VALIDTO FROM DBO.DEPARTMENT FOR SYSTEM_TIME BETWEEN '2014.01.01' AND '2015.09.13'

Considerations and Limitations

• A temporal table must have a primary key defined in order to correlate records between the current table and the history table, and the history table cannot have a primary key defined.
• The SYSTEM_TIME period columns used to record the SysStartTime and SysEndTime values must be defined with a datatype of datetime2.
• If the name of a history table is specified during history table creation, you must specify the schema and table name.
• By default, the history table is PAGE compressed.
• If current table is partitioned, the history table is created on default file group because partitioning configuration is not replicated automatically from the current table to the history table.
• Temporal and history tables cannot be FILETABLE and can contain columns of any supported datatype other than FILESTREAM since FILETABLE and FILESTREAM allow data manipulation outside of SQL Server and thus system versioning cannot be guaranteed.
• While temporal tables support blob data types, such as (n)varchar(max), varbinary(max), (n)text, and image, their will incur significant storage costs and have performance implications due to their size. As such, when designing your system, care should be taken when using these data types.
• History table must be created in the same database as the current table. Temporal querying over Linked Server is not supported.
• History table cannot have constraints (primary key, foreign key, table or column constraints).
• In SQL Server 2016 Community Technology Preview 2 (CTP2), temporal tables cannot be used in conjunction with In-Memory OLTP.
• INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns. Attempts to insert values directly into these columns will be blocked.
• TRUNCATE TABLE is not supported while SYSTEM_VERSIONING is ON
• Direct modification of the data in a history table is not permitted.
• INSTEAD OF triggers are not permitted on either the current or the history table to avoid invalidating the DML logic. AFTER triggers are permitted only on the current table. They are blocked on the history table to avoid invalidating the DML logic.

Here are the issues with temporal tables:

1. Data loads and reporting happens at the same time so it is not possible to do maintenance on historic table because it will break queries using temporal clause. So it will cause downtime.
2. Temporal table create unnecessary versions, which has to be cleaned manually. It is extra development work. It requires downtime.
3. System Dates are not always matching business dates. In order to match it you need update values in historic table. And it requires downtime.
4. it’s true that temporal may generate multiple versions but only in the case when updates really occur on the row. Even in that case you are able to query the latest state of the data (at the end of the day, for example) by specifying correct parameter in FOR SYSTEM_TIME AS OF query.  AS OF query returns correct results, regardless of how many updates occur (0, 1 or multiple).
5. To make a long story short - system-versioned temporal tables have some limitations when users need more control over the period of validity and versions but at the same time that property makes them a very good candidate for auditing and automated versioning scenarios.

Therefore, instead of extending system-versioned tables to support those scenarios, we may choose to introduce new type of temporal to allow users to edit period columns and history through regularly supported update operation, without downtime. Columnstore based tables are perfect in a combination with temporal tables.