Concept and basics of Temporal tables in SQL Server 2016

What are temporal tables?

Temporal (system-versioned) tables store and save data changes in a table so that you can return back to it in any time. This feature is very helpful, in day to day work, for developers because many times they asked about old backups to check on some data issues or changes. With this feature there is no need now since everything will be hosted in this system-version tables themselves. With temporal table the value of each record in any time can be determined easily rather than just the current value of each record.

Why Temporal tables?

• Data auditing – to check the values of a specific column
• Track slowing changing dimensions –to be able to track the slowing changing values
• Repair data corruption errors –we can retrieve the data from the historical data and by this we can fix any corrupted data
• Real data sources are dynamic
• No change in programming model or effect on the application

And one of the biggest advantages is that we don’t need to do workarounds to retrieve historical data and we don’t need to create custom data tracking. We just need to focus on the business logic of the solution and use the newly introduced query construct to query the data.

How does temporal table work

After enabling the temporal table feature, in any table, SQL Server 2016 will manage the two tables; the current table and the historical table (System-Versioned). The current table (physical table) will contain the last data, after the modification, and the historical table will contain the pervious values for each row after the modification.

SQL server 2016 introduced query constructs query data from these tables in a single query by using one of the following operations.

• Point in time: AS OF <date_time>
• Exclusive bounds: FROM <start_date_time> TO <end_date_time>
• Inclusive lower bound, exclusive upper bound: BETWEEN <start_date_time> AND <end_date_time>
• Inclusive bounds: CONTAINED IN (<start_date_time> , <end_date_time>)

Considerations and limitations:

There are some requirements and limitations for temporal tables we should be aware of including

1. A Primary Key is required in the current table (System-versioned)
2. A History table must be created in the same database as the current table
3. Linked servers are not supported
4. INSERT and UPDATE statements cannot reference SYSTEM_TIME period columns
5. The Truncate table operation is not supported on temporal tables
6. Always On supported
7. A System-Versioned table does not allowed any constraints
8. You can’t modify the data in history table “System-Versioned”
9. Durable memory-optimized tables can be system-versioned
10. Temporal table support Partitioning and column store index

For more information check this Microsoft Reference.

Temporal tables vs CDC

It depends on your business case as each feature has different functionality and usage

• Temporal Tables can give us a version of the current table at any point in time
• Change data capture change tracking (aka CDC) can help us for determining changes but it will not help us for reviewing (or working with) historical data

Creation and configuration

1. Create a new table with the enabled system-versioned table feature

To do it we should consider some important things in our implementation for this table

• System-Versioned table should have primary key to manage the relation between current table and historical table
• The Table should have two columns with data type DATETIME2 to use for the start and end of the row period
• Two columns used in the row period with data type (DATETIME2) should be NOT NULL

These two-period columns we can mark as HIDDEN columns, and they will not appear in the results:

creating a Temporal TableCREATE TABLE [dbo].[Countries](       [CountryID] [int] Primary KEY IDENTITY(1,1) NOT NULL,       [CountryArName] [nvarchar](255) NULL,       [CountryEnName] [varchar](255) NULL,       [IsReserved] [bit] NOT NULL CONSTRAINT [DF_Countries_IsReserved]  DEFAULT ((0)), [StartTime] DATETIME2 GENERATED ALWAYS AS ROW START               HIDDEN NOT NULL,       [EndTime]   DATETIME2 GENERATED ALWAYS AS ROW END              HIDDEN NOT NULL ,       PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   ) WITH(SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.CountriesHistory))

Now, after we executed the above script, if we opened SQL Server management studio we will find the new table as system-versioned, and inside this table you will find the corresponding historical table for it.

DML operations on Temporal Tables:

Insert: we will push some data in the table “Countries” by using simple insert statement:

INSERT INTO [dbo].[Countries]            ([CountryArName]            ,[CountryEnName]            ,[IsReserved])      VALUES            ('Egypt'            ,'Egypt'            ,1) GO

I haven’t mentioned the two DATETIME2 columns (Starttime , Endtime) because they are implemented as hidden columns and won’t impact anything in the application

After we executed the above Select statement we found the data inserted in the source table but there was not any record in the historical table. By default, when selecting using ( * ) the hidden period columns don’t not appear. To query these columns you must reference them explicitly, like this:

1	Select * , Starttime , Endtime from [Countries]

Update:

Let’s EXEC this update statement to update the CountryEnName and the check the execution plan to see which tables are affected by this statement:

1 2 3

Update [Countries] Set [CountryEnName] = 'KSA' where CountryID = 1

As we see in the execution plan, this update statement inserted data in the historical table. Now let us check the historical table:

We will discover that the data that existed before the record was updated was inserted into the historical table and we can found the StartTime and EndTime for this operation in the historical table:

Cleaning up temporal tables

Delete

1	Delete from [dbo].[Countries] where CountryID = 1

After the execution of the delete statement you will find that is affected on the both tables {Current table, historical table}

Truncate

Now if we need to truncate the source table “temporal table” it will not work

1	TRUNCATE TABLE dbo.[Countries]

Note: We can’t also do any DML operation on the history table “system-versioned”

Drop

To drop the temporal table, we should do follow the below sequence to be able to drop the table

First if you executed the simple drop statement like this it will not work.

1	Drop table [dbo].[Countries]

So we need to follow the below sequence for cleaning the Temporal table:

1 2 3 4 5 6 7

ALTER TABLE dbo.Countries SET (SYSTEM_VERSIONING = OFF) GO ALTER TABLE dbo.Countries DROP PERIOD FOR SYSTEM_TIME GO DROP TABLE dbo.Countries DROP TABLE dbo.CountriesHistory