Distinguish Temporal Tables In Azure SQL Database

In this article I am going to provide more details about temporal tables that were recently launched by Microsoft Azure Preview: Temporal tables in Azure SQL Database. Earlier, when developers wanted to search or track the history of a database they needed to write some custom code, but this new feature, temporal tables, will allow you to figure out the full history of your database without any custom code.

What are Temporal Tables? As mentioned above this is a new feature that helps you to figure out all the history of your database. Now please note that every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified. Other than this, temporal tables also contain a reference to another table with a mirrored schema. Please follow Temporal Tables by MSDN for more details.

Advantage of Temporal tables:

Help you to add data auditing for your applications.
Version your data.
Help you to search all data history without much code.
In case of any crash by application developer it helps you to perform fine-gained repairs.
Based on the time in database it will help you to calculate trends.
Azure SQL Database allows you to perform point-in-time querying very easily, using the FOR SYSTEM_TIME clause.
Temporal Tables ensure that historical data cannot be altered, even by users with edit permissions.

Ways to implement Temporal tables:

There are two terms we can discuss as a basic user. The first time if you are going to create a brand new application and you want to implement data auditing in your applications, and secondly is if the customer has an existing working app and wants to check data history. Below are the approaches.

Create New table as Temporal table:

I am simply showing you a SQL statement to creating a new table as temporal tables; just remember that mandatory elements of every temporal table are the PERIOD definition and the SYSTEM_VERSIONING clause with a reference to another user table that will store historical row versions. Two important points to remember are that every temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END and The PERIODcolumn should be not null.
1. CREATE TABLE MyDog
2. (
3. DogID int NOT NULL PRIMARY KEY CLUSTERED
4. , DogName varchar(50) NOT NULL
5. , OwnerID int NULL
6. , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
7. , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
8. , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
9. )
Once our temporal table is created in SQL server than it will automatically create a new table for
data auditing, for example mentioned in below screen shot using SQL 2012.

Just in case if you want to give a very good name to your history table please use below SQL statement.
1. CREATE TABLE MyDog
2. (
3. DogID int NOT NULL PRIMARY KEY CLUSTERED
4. , DogName varchar(50) NOT NULL
5. , OwnerID int NULL
6. , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
7. , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
8. , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
9. )
10. WITH
11. (
12. SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyDogHistory)
13. )
14. ;
Article mentioned at MSDN Creating a System-Versioned Temporal Table will explain this to you in more details.
Alter Existing table as Temporal table: Let's assume we have table MyDog already existing and we want to create this table as a temporal table then you need to do nothing just use below SQL statement.
1. ALTER TABLE MyDog
2. ADD
3. SysStartTime datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN
4. constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
5. , SysEndTime datetime2 (0) GENERATED ALWAYS AS ROW END HIDDEN
6. constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
7. , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
9. ALTER TABLE MyDog
10. SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyDogHistory));
11. GO
13. CREATE CLUSTERED COLUMNSTORE INDEX MyDogHistory
14. ON dbo.MyDogHistory
15. WITH (DROP_EXISTING = ON);
For more details, follow MSDN article about how to Alter Non-Temporal table to be system-versioned Temporal table.
So now you are ready to go, data audit is a just click away following simple SQL will explain how a change was made on a particular dog overa period.
1. SELECT * FROM MyDog
2. FOR SYSTEM_TIME BETWEEN '2015-01-01' AND '2016-01-01'
3. WHERE OwnerID = 1000 ORDER BY SysStartTime;
FOR SYSTEM_TIME filters out records that have a period of validity with zero duration (SysStartTime = SysEndTime). This MSDN article will explain more in details about FOR SYSTEM_TIME.

Conclusion:

This new step in SQL azure will really help customers for data auditing and monitoring; you should try this today. For detailed information on Temporal Tables, check out MSDN documentation. Visit Channel 9 to hear a real customer's temporal implemenation success story and watch a live temporal demonstration.