Temporal in SQL Server 2016

The term “temporal data” refers to records that are versioned in the database. For any given logical record, there is a current version and zero or more prior versions. Both the current and any previous versions are represented by physical rows in the database, though not necessarily in the same table.

Data integrity is hard when working with temporal tables. Each time a row is updated, there needs to be a way to ensure the current versions of the row in copied into the prior version table. This can be done with triggers or stored procedures, but both have their problems.

Querying temporal data is likewise challenging. Though you can easily get the current version of a logical record, retrieving the version of a particular data requires a complex and often error prone query. This often leads developers to invest in databases that are specialized for this type of workload.

SQL Server 2016 is offering another alternative, the new Temporal Table object. Superficially, a Temporal Table looks just like a normal table. It supports most column types, normal indexes, columnstore indexes, foreign keys, etc. CRUD style operations work as expected using normal SQL or an ORM. In fact, you can convert most normal tables into Temporal Tables without changing the stored procedures and applications that use said table.

Implementation wise, a Temporal Table is really two tables. One table contains the current values while another handles the historic versions of the data. The tables are linked so that any UPDATE or DELETE operation in the normal table automatically creates a corresponding history row. (INSERT operations don’t create historic records.)


Design Rules

Temporal tables need to have a SysStartTime and SysEndTime column, both being a non-nullable DateTime2. These columns can be named anything and are managed by SQL Server; users are not allowed to insert or update values into these columns.
The column type FILESTREAM is not supported, as it stores data outside of the database.
For table Foo, the history table will be named “FooHistory” by default. This can be overridden.
The history table cannot be modified directly, you can only add to it by updating or deleting data from the current table.
INSTEAD OF triggers are not supported and AFTER triggers are only allowed on the current table.


More Information:


http://sqlmag.com/sql-server/first-look-system-versioned-temporal-tables-part-1-creating-tables-and-modifying-data

http://tsql.solidq.com/resources.htm

http://www.pragimtech.com/Home.aspx