Temporal table usage scenarios

Use temporal system-versioning on tables that store critical information for which you need to keep track of what has changed and when, and to perform data forensics at any point in time.

Temporal system-versioned tables allows you to plan for data audit scenarios in the early stages of the development cycle or to add data auditing to existing applications or solutions when you need it.

The following diagram shows an Employee table scenario with the data sample including current (marked with blue color) and historical row versions (marked with grey color).

The right-hand portion of the diagram visualizes row versions on time axis and what are the rows you select with different types of querying on temporal table with or without SYSTEM_TIME clause.


If you have identified information that needs data audit, create database tables as temporal system-versioned. Data recovery victoria bc The following simple example illustrates a scenario with Employee information in hypothetical HR database:

In typical data audit scenario, you would query for all data changes that were applied to an individual row within a period of time of interest. 10k database The default history table is created with clustered row-store B-Tree to efficiently address this use case. Data recovery software mac Performing data analysis

After enabling system-versioning using either of the above approaches, data audit is just one query away from you. Cost of data recovery from hard drive The following query searches for row versions for Employee record with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

Unlike data audit, where the focus is typically on changes that occurred to an individual records, in time travel scenarios users want to see how entire data sets changed over time. S cerevisiae database Sometimes time travel includes several related temporal tables, each changing at independent pace, for which you want to analyze:

There are many real-world scenarios which require time travel analysis. Snl database To illustrate this usage scenario, let’s look at OLTP with auto-generated history. Database 4500 OLTP with Auto-Generated Data History

In transaction processing systems, it is not unusual to analyze how important metrics change over time. Data recovery miami fl Ideally, analyzing history should not compromise performance of the OLTP application where access to the latest state of data must occur with minimal latency and data locking. Data recovery sd card System-versioned temporal tables are designed to allow users to transparently keep the full history of changes for later analysis, separately from the current data, with the minimal impact on the main OLTP workload.

For high transactional processing workloads, we recommend that you use System-Versioned Temporal Tables with Memory-Optimized Tables, which allow you to store current data in-memory and full history of changes on disk in a cost effective way.

The data flush task with memory-optimized tables performs best under heavy OLTP workload when the history table has a clustered columnstore index.

Using temporal tables with in-memory OLTP reduces the need to keep the entire data set in-memory and enables you to easily distinguish between hot and cold data.

The following code example creates ProductInventory as an in-memory system-versioned temporal table with a clustered columnstore index on the history table (which actually replaces the row-store index created by default): Note

Make sure that your database allows creation of memory-optimized tables. Uottawa database See Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure.

The spUpdateInventory stored procedure either inserts a new product in the inventory or updates the product quantity for the particular location. Top 10 data recovery software free download The business logic is very simple and focused on maintaining the latest state accurate all the time by incrementing / decrementing the Quantity field through table update, while system-versioned tables transparently add history dimension to the data, as depicted on the diagram below

Dimensions in data warehousing typically contain relatively static data about entities such as geographical locations, customers, or products. Database query optimization However, some scenarios require you to track data changes in dimension tables as well. Qmobile data recovery Given that modification in dimensions happen much less frequently, in unpredictable manner and outside of the regular update schedule that applies to fact tables, these types of dimension tables are called slowly changing dimensions (SCD).

When you choose SCD strategy, it is responsibility of the ETL layer (Extract-Transform-Load) to keep dimension table(s) accurate and that usually requires a lot of code and complex maintenance.

System-versioned temporal tables in SQL Server 2016 can be used to dramatically lower the complexity of your code as history of data is automatically preserved. No 1 data recovery software Given its implementation using two tables, temporal tables in SQL Server 2016 is closest to Type 4 SCD. Data recovery iphone free However, since temporal queries allows you to reference current table only, you can also consider temporal tables in environments where you plan to use Type 2 SCD.

In order to convert your regular dimension to SCD, just create a new one or alter an existing one to become a system-versioned temporal table. Database 11g If your existing dimension table contains historical data, create separate table and move historical data there and keep current (actual) dimension versions in your original dimension table. Data recovery thumb drive Then use ALTER TABLE syntax to convert your dimension table to a system-versioned temporal table with a predefined history table.

The following example illustrates the process and assumes that the DimLocation dimension table already has ValidFrom and ValidTo as datetime2 non-nullable columns which are populated by the ETL process:

Using system-versioned temporal tables for SCD is acceptable if period of validity calculated based on database transaction time is fine with your business logic. Data recovery hardware tools If you load data with significant delay, transaction time might not be acceptable.

By default, system-versioned temporal tables do not allow changing historical data after loading (you can modify history after you set SYSTEM_VERSIONING to OFF). Os x data recovery This might be limitation in cases where changing historical data happens regularly.

Temporal system-versioned tables generate row version on any column change. R studio data recovery download If you want to suppress new versions on certain column change you need to incorporate that limitation in the ETL logic.

If you expect a significant number of historical rows in SCD tables, consider using a clustered columnstore index as the main storage option for history table. Data recovery western digital That will reduce history table footprint and speed up your analytical queries.

banner