Temporal Tables allow you to track the full history of data changes directly in Azure SQL Database, without the need for custom coding. With Temporal Tables you can see your data as of any point in time in the past and use declarative cleanup policy to control retention for the historical data.
When to use Temporal Tables?
Quite often you may be in the situation to ask yourself fundamental questions: How did important information look yesterday, a month ago, a year ago, etc. What changes have been made since the beginning of the year? What were the dominant trends during 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 improve your productivity when you develop applications that work with ever-changing data and when you want to derive important insights from the changes.
Use Temporal Tables to:
- Support data auditing in your applications
- Analyze trends or detect anomalies over time
- Easily implement slowly changing dimension pattern
- Perform fine-grained row repairs in case of accidental data errors made by humans or applications
Manage historical data with easy-to-use retention policy
Keeping history of changes tends to increase database size, especially if historical data is retained for a longer period of time. Hence, retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table. Temporal Tables in Azure SQL Database come with an extremely easy-to-use retention mechanism. Applying retention policy is very simple: it requires users to set single parameter during the table creation or table schema change, like shown in the following example.
ALTER TABLE [WebSiteClicks]
SET
(
SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo. WebSiteClicks_History,
HISTORY_RETENTION_PERIOD = 3 MONTHS
)
);
You can alter retention policy at any moment and your change will be effective immediately.
Why you should consider Temporal Tables?
If you have requirements for tracking data changes, using Temporal Tables will give you multiple benefits over any custom solution. Temporal Tables will simplify every phase in the development lifecycle: object creation, schema evolution, data modification, point-in-time analysis and data aging.
Next steps
To learn how to integrate Temporal Tables in your application, read the following article with the step-by-step instructions. To utilize temporal retention, check out Manage temporal history with retention policy article on Azure.com.
Visit Channel 9 to hear a real customer story and watch a live presentation with the demo. For more information, check out MSDN documentation.