Temporal tables are a powerful feature introduced in SQL Server 2016 that allow application developers to view the state of data at a specific point in time. They can also be used for logging purposes if required. In this article, we will explore how this feature can be used and discuss some potential use cases.

Let’s start by creating a database and a temporal table with the temporal history database automatically created:

USE TemporalDB
GO

-- Create a simple temporal table with a history table automatically created
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

Now that we have created the temporal table, let’s see how we can retrieve data from a specific point in time. We can use the system tables to get information about the temporal table and its associated history table:

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

The output of the above query will show the original table and its associated history table. These tables are linked, which means that it is not possible to drop the main table without stopping the system versioning. To drop the tables, we need to first stop versioning and then drop both the main table and the history table:

USE [TemporalDB]
GO

-- Stop versioning
ALTER TABLE [dbo].[TemporalExample] SET (SYSTEM_VERSIONING = OFF)
GO

-- Drop the main table
DROP TABLE [dbo].[TemporalExample]
GO

-- Drop the history table
DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_565577053]
GO

It is important to note that the history table name may be different in your database, so make sure to adjust the code accordingly.

Temporal tables can be used in various scenarios, such as auditing changes to data, tracking historical data for compliance purposes, and analyzing trends over time. They provide a convenient way to query data as it existed in the past, without the need for complex queries or manual backups.

In conclusion, temporal tables in SQL Server 2016 offer a powerful feature for managing and querying historical data. By understanding how to create and use temporal tables, you can enhance your database applications and gain valuable insights from historical data.

We hope you found this article interesting. Stay tuned for more articles on SQL Server and its features.