T-SQL Tuesday #79 - SQL Server 2016 temporal tables are just system history tables (for now)
I have chosen to blog about the usage of SQL 2016 temporal tables for T2 history in data warehouses.
SQL Server 2016 have a lot of new, great features. One of them is called "(system-versioned) temporal tables." Since I'm currently watching a masterclass by Chris Date on "Temporal Data and Relational Theory," I was curious and checked out what Microsoft are covering in SQL Server 2016.
System-versioned temporal tables in SQL Server 2016 enables you to track all changes to a table to a shadow table, with a table name either defined by you or the database. This way, transaction-time history can be established. Furthermore, the data in history tables cannot be tampered with (so no INSERTs, UPDATEs or DELETEs are allowed). It is possible to query a system-versioned temporal table "back in time" with the "FOR SYSTEM_TIME AS OF" syntax:
SELECT col1, ..., col3 FROM table FOR SYSTEM_TIME AS OF '1972-12-28' ;
This is a great feature for OLTP systems with a need to track not only who did which changes (normal audit), but also what did change. In my job at the IT University of Copenhagen, we had this implemented in a number of places, and especially for some users, it was good to have proof that the data problems were because of them and not because the IT system didn't work.
Traditionally, this kind of data change tracking is implemented with a user-defined history table and a trigger firing on updates and deletes. Not hard to code, but requires either a lot of discipline or auto-generating history tables and trigger code. Needs additional discipline in deployments.
Will SQL Server 2016 temporal tables be helpful in data warehouses, then? Could we get rid of custom hand made (or auto generated) type 2 history logic, and just use system-versioned temporal tables? No. First of all, when we have dimensions with type 2 history, our fact tables need to be able to reference the version of the dimension row. With system-versioned temporal tables, the current rows reside in the table and the history rows reside in the history table. Secondly, we want to be able to control the type 2 history at load time, making it possible to roll back batch loads and rerun loading parts of the data warehouse. With system-versioned temporal tables, we cannot control the definition of history (we have transaction-time history, not user-defined history), and we cannot delete data from the history table (which we would need during a rollback of a batch load).
Microsoft have a page in the MSDN documentation with usage scenarios on data warehouse history and system-versioning. See link below. I don't agree. But then, we don't always have to agree... :)
Read more about system-versioned temporal tables in the SQL Server documentation here:
Read more about how system-versioned tables according to Microsoft could be used in data warehousing here:
Read more about system-versioned temporal tables in the SQL2011 standard in this paper:
Kulkarni, Krishna, and Jan-Eike Michels. "Temporal features in SQL: 2011". ACM SIGMOD Record 41.3 (2012): 34-43: