Hello Friends,

Several new features announced by Microsoft in SQL Server 2016. We have seen a few of them in my previous blog posts. In the same row, we will bring a look today on next interesting feature i.e. SQL Server 2016 – Temporal Tables. Actually, temporal feature was  introduced in ANSI SQL 2011. Microsoft has taken some time to implement it and announced this feature in SQL Server 2016. This feature is available in all editions of SQL Server 2016.

Usually, tables in the SQL Server databases stored the current state of data. For example- let’s say we have a table which stores the details of customers. I made a change in customer contact number yesterday. Can I query that same table to see the contact number of the customer along with the contact number before changes made by me yesterday?  By using system-versioned temporal tables, we can see all these details very well.

The full name of this feature is system-versioned temporal tables. Here the term ‘system versioned’ means SQL Server will maintain the period of validity for the row(s). Due to this we have to define two columns with datetime2 data type. These columns will store the period information and these columns will be used by SQL Server internally.

If we want to make a table as a temporal table then we have to create that table along with “WITH (SYSTEM_VERSIONING = ON)”. Let me show you the same thing practically.

Here LifeStart and LifeEnd with period for system_time both will be used by SQL Server internally to store the valid period information. Both of these columns can not be specified as NULL. Till now, almost everything looks like the normal database table.

Due to SYSTEM_VERSIONING=ON, by default, SQL Server will automatically:

  • Creates an another table named as MSSQL_TemporalHistoryFor_<TemporalTableObjectID> under the same schema with same structure except constraints.
  • Creates a row store clustered index as ix_MSSQL_TemporalHistoryFor_<TemporalTableObjectID>

This automatically created table will store all the information about data changes. You can see the same detail in Object Explorer:

SQL Server 2016 - Temporal Tables 2

Here the name of the automatically created table is not looking good and even not readable because of object id of the table. You can specify the name for history table by using HISTORY_TABLE as mention in below TSQL code:

SQL Server 2016 - Temporal Tables 3

In the next blog post we will look into some more details about temporal tables.



Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook