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

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.

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'.

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

Result:

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:

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.

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:
  

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:

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)