Temporal data is going to be an integrated part of the SQL Server engine as of 2016. What does that mean for you, the consumer, the writer of SQL Queries? It means that you turn on temporal behavior through DDL commands (Data Definition Language) used to define your tables. Then, you use an extension of the DML (Data Manipulation Language) to query the historical data.
Firstly, when you create your database table you define the historical storage. You can do it when you create the table, or alter an existing table to add temporal storage. Temporal storage is implemented in a mirror table with the same schema, and additional temporal columns. You don’t do anything except specify the temporal table. When records are updated or deleted, the historical records are stored in the temporal table.
Only storing changes in the temporal table is done for performance. If you do not use temporal keywords in a select statement, the records from the base table are the only ones queried. If you include temporal statements, then the history, or the history and current table are queried, depending on the type of temporal query you write.
This technique is one that is often used, and home grown in the past. Now, when you have SQL Server 2016, you can simply define a table as temporal, and all the plumbing is already done for you. There’s a lot you can do with temporal tables. Borko Novakovic has a great presentation you can watch on SSWUG.TV entitled “Temporal and JSON in SQL Server 2016”. If you can’t open the video consider upgrading your membership to Pro. It will grant access to this, and so much more.
IMHO, this is one of the coolest features to be added to the SQL Engine in a long time. I hope you will find it useful as well.
Cheers,
Ben