Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
What is a temporal table in SQL Server?
We can store the history of inserts, updates and deletes by using temporal tables. Previously, we could only store the latest version of the data. And if someone perform an incorrect update, we could have solved the problem by using the backup. Other benefits of temporal tables are listed below.
Temporal table allows us to analyze data at a certain time in the past thanks to we store a history of data.
All temporal tables have two columns, datetime2 type, Period start column and Period end column. These columns are called period columns. These period columns are used to store how long this change takes effect when a row changes. I think it would be more clearly understood with an example.
Period start column stores the moment the transaction starts. For example, you started a transaction and performed multiple transactions in that transaction. The value in the period start column is the start time of the transaction for all transactions, regardless of the end time of the transaction.
Temporal tables also maintain the same table’s schema structure in another table (history table). When there is an update or delete operation in the temporal table, the version before this change is stored in the history table.
When creating the temporal table, you can create another table with the same schema structure as the temporal table and specify it as history table. If you do not specify, the history table will be created automatically by the sql server. History tables have period columns as in temporal tables.
Benefits of Temporal Tables
• You can audit the operations performed by the application developers. Thus, transactions are recorded.
• You can reconfigure a version of the data at a time in the past.
• You can see the change curve of data for KDS applications.
• If an incorrect action has been taken, you can undo it. As database administrators, I would say that there is a solution that saves us from a huge workload.
Create a Temporal Table
Let’s create a temporal table as follows. As you can see in the script, the table name is TemporalExample. The name of the History table is TemporalExample_HistoryTable.
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE dbo.TemporalExample ( [ID] int NOT NULL PRIMARY KEY CLUSTERED , [Name] VARCHAR(100) NOT NULL , [Surname] VARCHAR(100) NOT NULL , [Salary] INT NOT NULL , [PeriodStart] datetime2 (2) GENERATED ALWAYS AS ROW START , [PeriodEnd] datetime2 (2) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (PeriodStart, PeriodEnd) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalExample_HistoryTable)); |
After creating the table, insert some records.
1 2 3 4 5 | INSERT INTO [dbo].[TemporalExample]([ID],[Name],[Surname],[Salary]) VALUES (1,'Nurullah','CAKIR',1000), (2,'Hakan','GURBASLAR',1000), (3,'Faruk','ERDEM',1000), (4,'Dilara','AYDIN',1000) |
Then, to see what’s happening, let’s query the TemporalExample table and the TemporalSample_HistoryTable table as follows.
1 2 3 | SELECT * FROM [test].[dbo].[TemporalExample] GO SELECT * FROM [test].[dbo].[TemporalExample_HistoryTable] |
As you can see below, the PeriodStart and PeriodEnd columns are set automatically.
The PeriodStart column shows the start time of the transaction,
‘9999-12-31 23: 59: 59: 59’ was set in the PeriodEnd column.
We said that the previous version of the changes was stored in the History table. So we see that the history table is still empty.
Now let’s perform an update to see how the data is stored in the history table.
As you can see in Script I’m raising my own salary 1 dollar. 🙂
1 | UPDATE [dbo].[TemporalExample] SET [Salary] = 1001 WHERE Name='Nurullah' |
I’m running the same select statements again after I run the Update query.
As you can see below, the line of Nurullah has a Salary of 1001 and the PeriodStart column has changed.
We also see a new record in the History table. In this table we see the old Salary value.
In the PeriodStart column we see the time when this value of this row is set.
In the PeriodEnd column, we see when this value is updated.
Let’s delete the same line this time and run the select statements again.
1 | DELETE [dbo].[TemporalExample] WHERE Name='Nurullah' |
As you can see, a row is added to the history table each time data is changed.
The final version of our tables is as follows:
With the help of the following query, you can query the records in a specific date range with the version information by using both tables.
Note: Change the time according to current date.
1 2 3 4 | SELECT * FROM [test].[dbo].[TemporalExample] FOR SYSTEM_TIME BETWEEN '2019-11-28 13:46:00.00' AND '2019-11-28 13:56:00.00' WHERE Name= 'Nurullah' ORDER BY PeriodStart; |
With the help of the following query, you can query all records with version information by using both tables.
1 2 3 | SELECT * FROM [test].[dbo].[TemporalExample] FOR SYSTEM_TIME ALL ORDER BY PeriodStart; |
If you run the DROP TABLE script to delete temporal tables, you will receive an error like the one below.
Msg 13552, Level 16, State 1, Line 12
Drop table operation failed on table ‘test.dbo.TemporalExample’ because it is not supported operation on system-versioned temporal tables.
In order to perform the deletion, you must first turn off the versioning in the table. You can delete your temporal tables by turning off versioning with the following script.
1 2 3 4 5 | ALTER TABLE [dbo].[TemporalExample_HistoryTable] SET ( SYSTEM_VERSIONING = OFF ) GO DROP TABLE [dbo].[TemporalExample] GO DROP TABLE [dbo].[TemporalExample_HistoryTable] |
By default, there is a clustered index on the periodstart and periodend columns in the history table. For better performance, I recommend creating a history table manually and creating a clustered columnstore index on these two columns.
Although it sounds like a nice feature at first. But there are some limits and restrictions. Below you can find its limits and restrictions.
Temporal Table Limitations and Restrictions
• There must be Primary in the main table.
• If the main table is partitioned, the history table is created in the default file group.
• The main and history tables can not be file tables.
• Although the main table supports data types such as (n) varchar (max), varbinary (max), (n) text, and image, there is a significant storage cost when you create the temporal table with these data types. There may also be a performance problem due to the size of these data types.
• The History table cannot contain primary key, foreign key, table, or column constraint.
• No indexed view support.
• TRUNCATE and DROP TABLE are not supported when system versioning is on.
• The main table does not support ON DELETE CASCADE and ON UPDATE CASCADE. You can find out what these statements mean in the article “What is ON DELETE CASCADE and ON UPDATE CASCADE”. This is supported in SQL Server 2017 CTP 2.0.
• INSTEAD OF trigger is not supported. AFTER Trigger is only supported for Main table. You can find detailed information about trigger types in the article “SQL Server Trigger Types”.
• Supports Change Data Capture and Change Tracking only for the main table. You may want to read the article “Change Data Capture (CDC)”.
• No merge replication support.
Create a Memory Optimized Temporal Tables
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE dbo.TemporalExampleInMemory ( [ID] int NOT NULL PRIMARY KEY NONCLUSTERED , [Name] VARCHAR(100) NOT NULL , [Surname] VARCHAR(100) NOT NULL , [Salary] INT NOT NULL , [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL , [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL , PERIOD FOR SYSTEM_TIME (PeriodStart, PeriodEnd) ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA, SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.TemporalExampleInMemory_HistoryTable ) ); |
