SQL SERVER 2016: Creating Simple Temporal Table

Temporal tables are a new feature that was introduced in SQL Server 2016. A temporal table gies application developers to view the state of data at a point in time. Temporal tables can also be used for logging purposes if required. I wanted to explore how this feature can be used. The documentation has been interesting and I went ahead in trying out how this can be enabled.

One of the most used use case for this feature would be to find the data values in a past date. This can now be easily configured in SQL Server 2016. Let us go ahead and create a database for use and then create a table with the Temporal history database created by default / automatically. The syntax for this would look like:

USE TemporalDB
GO
--Simple temporal table with history table automatically created
--(name of history table is MSSQL_TemporalHistoryFor_<object_id of table>) :
CREATE TABLE dbo.TemporalExample
(
ID INT NOT NULL PRIMARY KEY,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL ,
Period FOR system_time (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON)
GO

As you can see below, in my SQL Server 2016 database, the table has been created and the system generated history table is linked with it.

SQL SERVER 2016: Creating Simple Temporal Table temporal-table-01

I wanted to understand if we can get the same value from system tables. I have used the sys.tables DMV to select the same.

SELECT name [Original_Table],
OBJECT_NAME(history_table_id) [History_Table_Name] FROM sys.tables
WHERE history_table_id IS NOT NULL

The output for this is shown below:

SQL SERVER 2016: Creating Simple Temporal Table temporal-table-02

Since these tables are linked, it is not possible to drop the main table without stopping the system version. Since we created the table, let us look at the possibility of dropping the tables. We are going to stop versioning and then drop both these tables.

USE [TemporalDB] GO
ALTER TABLE [dbo].[TemporalExample] SET ( SYSTEM_VERSIONING = OFF) 
GO
DROP TABLE [dbo].[TemporalExample] GO
-- Add the history table which was created in your databases
DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_565577053] GO

In the above code, your history table data would be different and please make the changes appropriately. Let us go ahead by cleaning the database that we created to experiment Temporal.

USE MASTER
GO
DROP DATABASE TemporalDB
GO

What are some of the use cases you think temporal will be used in your environment? We will surely blog more functions of using temporal in the future. I am sure you will find this interesting.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Server 2016, SQL Utility, Temporal Table
Previous Post
SQL SERVER – How to Connect Using NT AUTHORITY \ SYSTEM Account?
Next Post
SQL SERVER – JSON Support for Transmitting Data for SQL Server 2016 – Notes from the Field #114

Related Posts

2 Comments. Leave new

  • Thanks Pinal.In most of the organisation there is “no hard deleted” policy.Instead they create few audit column for “soft delete”.Or where there is “hard delete” policy they maintain history table for important table.So in such scenario “Temporal tables” will be beneficial.We do not have to write so much code to maintain history tables.Rest when we use it then only we come to know about its limitation.

    Reply
  • John Zabroski
    June 4, 2018 10:39 pm

    Hi Pinal,

    I’m a bit stumped on a problem I have. I’m trying to create a test environment and keep running into a showstopper error in SQL:

    Cannot delete rows from a temporal history table ‘UnitTest.Ftp.KeyHistory’.

    Reply

Leave a Reply