SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature

Views: 1.325
This post is the 10 part of 16 in the series. Security and Audit
Reading Time: 7 minutes

Hey guys!!

Through this post, I would like to show you a very interesting and useful feature, which is the Temporal Tables. As I already commented about them in the post SQL Server 2016 - News and Features List, where you can retrieve data from a table at any point in time. This means that you can take a “time travel”, visualizing what was the data for a given table at a specific date and time, using simple SQL queries.

This feature has a wide range of possibilities and utilities, of which I can highlight the Slowly changing dimensions BI to implement a dimension whose vision is that of a given date, and also for the purpose of Auditcomparing the past with the present. All without creating triggers and no manual controls.

It is worth mentioning that this feature is available in all editions of SQL Server, including Web Edition and Express.

How do Time Tables Work?

View content
A time table is nothing more than a regular table, which has a system versioning for writing change data to a history table (defined by you) with two DATETIME2 columns to control the validity of the record and thus allow that feature works. The original table will always keep the current data and the history table will record the old records as shown in the image below:

When querying the data specifying the desired date, records from the 2 tables will be considered transparently for you, as I will demonstrate in the next topic.

Constraints of a Time Table

View content
An important part of implementing a new feature is understanding how it works and its restrictions, which I will list below:

  • The table in question must have a PRIMARY KEY defined in order to use the versioning feature. Otherwise, you will see a message like this:
    Msg 13553, Level 16, State 1, Line 4
    System versioned temporal table 'dirceuresende.dbo.Tabela_Temporal' must have primary key defined.
  • Once you create a table with this feature enabled, you will not be able to perform the TRUNCATE TABLE operation on this table, resulting in the following error message:
    Msg 13545, Level 16, State 1, Line 58
    Truncate failed on table 'dirceuresende.dbo.Temporal_Table' because it is not supported operation on system-versioned tables.
  • You will no longer be able to delete the table that has an active versioning. To do this, you will need to stop versioning and then delete the table. Otherwise, you will see the following error message:
    Msg 13552, Level 16, State 1, Line 58
    Drop table operation failed on table 'dirceuresende.dbo.Tabela_Temporal' because it is not supported operation on system-versioned temporal tables.
  • In-memory OLTP Cannot Be Used
  • INSTEAD OF triggers are not allowed. AFTER triggers only allowed on current table (Cannot create on historical table).
  • Historical table cannot have constraints
    Msg 13564, Level 16, State 1, Line 1
    Adding CHECK constraint to a temporal history table 'dirceuresende.dbo.Tabela_Temporal_Historico' is not allowed.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint or index. See previous errors.
  • Data in historical table cannot be modified manually (UPDATE, DELETE, INSERT, etc.)
    Msg 13560, Level 16, State 1, Line 1
    Cannot delete rows from a temporal history table 'dirceuresende.dbo.Tabela_Temporal_Historico'.
  • INSERT and UPDATE statements cannot reference SYSTEM_TIME period columns
    Msg 13537, Level 16, State 1, Line 5
    Cannot update GENERATED ALWAYS columns in table 'dirceuresende.dbo.Tabela_Temporal'.

What tables are temporal in my base?

View content
To check which tables have system versioning enabled, just run this query below:

Result:

How to create a Time Table (Versioned)?

View content
To create a table with system versioning enabled, you will need to create 2 columns in your table to determine the validity of the records:

  • Dt_Home DATETIME2 GENERATED ALWAYS AS ROW START
  • End DATETIME2 GENERATED ALWAYS AS ROW END

And also the PERIOD FOR SYSTEM_TIME (Dt_Start, Dt_Fim) clause will be added after these columns and also the WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HistoricTable)) clause will be added to the end of your table's CREATE command.

Now I will demonstrate a complete CREATE TABLE to make it easier to understand what a time table would look like:

After creating a temporal table (system versioning), the SQL Server Management Studio Object Explorer screen will display the table as follows:

How to convert a common table to Temporal Table?

View content
To convert a common table to Temporal Table, just execute some ALTER TABLE commands to create the period definition columns (Dt_Start and Dt_Fim) and activate versioning in the table, as below:

What if the table already has records? Well, in this case, just create columns with a constraint to include a DEFAULT value in columns that will be created now and have no value:

With the above query, you will create the new columns with Dt_Start = current date / time and Dt_Fim = maximum date of datetime2, ie all records in the table will be considered the current records.

How to query the data from the Time Table?

View content
Now that we have created our table with system versioning enabled, turning our table into a temporal table or temporal table, let's generate some information and then visualize how we can query it:

Result of our table:

In the examples above, I used the AS OF 'data' clause. There are 5 types of clauses to specify the desired date range:

  • ALL: Return all changes made to the table

    Example:

  • AS OF: Point-in-time feature, ie returns data at a specific date and time.

  • FROM TO: Returns all records whose Dt_Initial field <informed end date and Dt_Final field> informed start date.

    Example:

  • BETWEEN AND: Returns all records whose Dt_Initial field <= informed end date and Dt_Final field> informed start date. This clause is very similar to FROM… TO .. ​​with the only difference being that the first condition also considers records with equal condition (<=), while the FROM .. TO .. ​​clause does not (only <)

    Example:

  • CONTAINED IN ( , ): Returns all records whose Dt_Initial> = start date informed and Dt_Final field <= end date informed.

    Example:

How to disable versioning of a Time Table?

View content
If you no longer want to use the system versioning feature by turning a time table into a common table and deleting all the time table metadata and structure, you just have to use the following commands:

If you just want to temporarily disable the versioning feature but don't want to delete the information, you can use the command below:

Will my history table grow forever?

View content
One caution you must take when enabling this feature on your instance is related to the disk space consumed by the history table. By default, the table will grow indefinitely until disk space is exhausted. To control this, you can create a job that will do the data cleansing process, keeping only the latest data, as examples below:

Azure SQL Database

SQL Server (On-Premise)

That's it folks!
A big hug and even more!