Introduction to Azure SQL Database Temporal Tables

As announced on June 1, 2016, SQL Server 2016 has reached its general availability. This means that you finally have the option to implement some of its new features in the production environment. While we still have to wait for their full support in Azure SQL Database V12, it is likely that their preview stage will be concluded soon. In this article, we will focus on Temporal tables, which is one example of these new features.

The primary purpose of Temporal tables is to provide the full visibility of historical changes of content stored in Azure SQL Database tables. Effectively, you have the ability to identify the state of your data at an arbitrary point in time. This comes in handy in a variety of scenarios, including performing analysis of usage trends of database-bound applications, identifying data lineage, or carrying compliance and regulatory audits. In addition, you have a rather unique ability of granular recovery of individual table entries, without resorting to traditional restore methods. All of these benefits can be realized without the need for programming or database design changes. Existing applications interact with their data sources in the same manner as they would without having temporal tables in place.

The new functionality leverages a couple of extra columns that must be included in the original table to determine the timeline of changes to its content, which subsequently are captured in the associated table dedicated to storing historical records. The two columns are of the datetime2 data type and represent, respectively, the beginning and end (designated in the table schema by the GENERATED ALWAYS AS ROW START, GENERATED ALWAYS AS ROW END, and PERIOD FOR SYSTEM_TIME options) of the period during which a particular row had a specific set of values. In addition, the table must have the SYSTEM_VERSIONING option enabled (set to ON). There are several additional requirements that must be satisfied in order for a table to support this functionality, including, for example the presence of the primary key or absence of INSTEAD OF triggers (for a comprehensive overview of Temporal Tables considerations and limitations, refer to the MSDN Library).

As you would expect, the history table does not support direct modifications. In addition, once the SYSTEM_VERSIONING option is enabled, you will not be able to modify the schema of the temporal table (to modify it, you would have to temporarily disable the system versioning). You can, however, manage independently the indexing of the history table (including dropping and creating new indexes). Considering that its size is likely to grow far beyond the size of the original table, you might consider taking advantage of the clustered columnstore indexes to maximize performance of index-based operations while keeping the index size in check. Note, however, that support for columnstore indexing requires the Premium service tier (temporal tables are available in all tiers of Azure SQL Database).

You can configure a new table as temporal during its creation (as part of the CREATE TABLE T-SQL statement or afterwards (by using the ALTER TABLE T-SQL statement. You also have the option of using the default history table generated automatically when enabling system versionign or point to an existing one as long as it satisfies all requirements outlined in Temporal Tables considerations and limitations MSDN Library article). All subsequent changes are automatically recorded in the history table.

To create a new temporal table, you would use a T-SQL statement containing the following code (which you can apply to the target Azure SQL database using any of traditional SQL Server management tools, including SQL Server Management Studio, SQL Server Data Tools, or even sqlcmd):

CREATE TABLE dbo.<temporal_table_name>
(  
(...)
  , [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.<history_table_name>));

Similarly, to modify an existing table by enabling system versioning functionality, you would run (note that we assign defaults to both datetime2 columns) the ALTER TABLE statement resembling the following:

ALTER TABLE dbo.<existing_table_name>
ADD 
    [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END HIDDEN   
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo); 

ALTER TABLE WebsiteUserInfo  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.<history_table_name>));
GO

This concludes our introduction to the Temporal tables functionality in Azure SQL Database. In our upcoming articles, we will provide examples of querying their content by using new T-SQL constructs available in Azure SQL Database V12.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles