Now available in public preview, you can use Temporal Tables, a new programmability feature on Azure SQL Database that allows you to track and analyze the full history of changes in your data, without the need for custom coding. Temporal Tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period.
Usage scenarios
Real data sources are never static: Critical information changes over time, while different application scenarios require us to analyze trends, with the ability to focus analysis to a specific point in time.
Users who track data changes aim to answer fundamental questions: How did facts look in a specific point in time in the past (yesterday, a month ago, a year ago, etc.)?, what changes have been made?, and when and what were the dominant trends in a specific period of time? Without proper support in the database, however, questions like these have never been easy to answer.
Temporal Tables are designed to be the productivity tool for developing applications that work with historical data – we strongly recommend using them in the scenarios described below, due to the huge productivity benefits. Note that Temporal Tables are available in all service tiers of Azure SQL Database.
Data audit
Enable temporal system-versioning on tables that store critical information, for which you need to keep track of what has changed, when and by whom, and to perform change forensics at any point in time. Temporal Tables allow you to plan for data audit scenarios in the early stages of the development cycle, or to add data auditing to existing applications when you need it.
If you develop new application, create key tables as temporal system-versioned; if you need to perform data audit in existing database, use ALTER TABLE to extend non-temporal tables to temporal. The following example illustrates how you can start tracking changes of employee related information in HR database, with a simple CREATE TABLE statement:
CREATE TABLE Employee
(
[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
, [Name] nvarchar(100) NOT NULL
, [Position] varchar(100) NOT NULL
, [Department] varchar(100) NOT NULL
, [AnnualSalary] decimal (10,2) NOT NULL
, [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
Once table is created, data audit is just one query away from you. The following simple query with the new FOR SYSTEM_TIME clause demonstrates how you can easily search for changes made to a particular employee over the period of interest:
SELECT * FROM Employee FOR SYSTEM_TIME BETWEEN '2014-01-01' AND '2015-01-01' WHERE EmployeeID = 1000 ORDER BY ValidFrom;
Point in time analysis (time travel)
Unlike data audit, where focus is on changes that occurred to individual records, in time travel scenarios you typically want to see how entire data set looked in the past or changed over time. Time travel may include multiple temporal tables, each changing at independent pace, for which you want to analyze:
- Trends for the important indicators throughout the history
- Exact snapshot of data “as of” any point in time in the past (yesterday, a month ago, etc.)
- Differences in between two point in time of interest (a month ago vs. three months ago, for instance)
Use new CREATE or ALTER TABLE syntax to configure important tables to be temporal, as described in data audit scenario.
Fixing incorrect data at the row-level
You can rely on historical data in Temporal Tables to quickly repair individual table rows to the “last good known state”, in case of accidental errors made by humans or applications. This is especially useful when you are able to locate affected rows or when you know the time of the undesired data change. Temporal Tables allow you to perform repairs efficiently, without dealing with backups. This approach has several advantages:
- You are able to control the scope of the repair very precisely. Rows that are not affected will stay at the latest state, which is often a critical requirement.
- The operation is very efficient and the database stays online for all workloads using the data.
- The repair operation itself is versioned. You have an audit trail for repair operation itself, so you can analyze what happened later if necessary.
For more details, check out the corresponding MSDN article.
Why you should consider Temporal Tables?
If you have a requirement for tracking data changes, using Temporal Tables gives you multiple benefits over a custom solution. Temporal Tables simplify every phase in the development lifecycle, from object creation through schema evolution and data modification, from data analysis to security:
- Table creation and schema evolution are supported via the well-known CREATE/ALTER table syntax and the rich table editor in SQL Server Development Tools . Schema operations on Temporal Tables automatically apply smart default options, optimized for typical usage scenarios.
- Change tracking occurs completely transparently to your workloads. While users and applications modify the data in the regular way, history is automatically built behind the scenes.
- Data analysis: Azure SQL Database allows you to perform point-in-time querying very easily, using the FOR SYSTEM_TIME clause. The power of temporal querying becomes apparent in scenarios of querying complex database models using views and including multiple tables with foreign key relationships “as of” a point in time in the past.
- Immutability of historical data: Temporal Tables ensure that historical data cannot be altered, even by users with edit permissions.
Next steps
To learn how to integrate Temporal Tables in your application, read the following article with the step-by-step instructions. Visit Channel 9 to hear a real customer story and watch a live presentation with the demo.
For detailed information, check out MSDN documentation. Finally, try out Temporal Tables today – we are eager to hear your feedback!