SQL SERVER 2016: Creating Simple Temporal Table

February 10, 2016

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)