Temporal Tables are generally available in Azure SQL Database

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:

  1. Support data auditing in your applications
  2. Analyze trends or detect anomalies over time
  3. Easily implement slowly changing dimension pattern
  4. 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.

temporaltableinazure

 

Advertisements
Temporal Tables are generally available in Azure SQL Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s