Programming & Development, SQL Server

Manage Change History Using System-Versioned Temporal Table

Contributors

Vivasoft Team

Tech Stack

0 +

Here we have an application that shares district and date wise updated current temperature, when temperature of any district with current date changes, we update that with current temperature. In our old system we only keep the district and date wise a new row but now we need total periodic change history for analysis purposes.

Suppose we want to see today’s periodic temperature ups-down history of Dhaka district; how can we implement that.

Here comes the System-Versioned Temporal Table. It is a type of user table designed to keep a full history of data changes, allowing easy point-in-time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system.

System-versioned temporal tables were introduced in SQL Server 2016, and they provide a way to track changes to data over time. With a temporal table, SQL Server automatically creates a history table that stores all of the changes made to the main table, including when the changes were made and who made them.

Creating a system-versioned temporal table in SQL Server is similar to creating a regular table, but with a few additional steps.

First, you need to enable system versioning for the table by specifying the PERIOD FOR SYSTEM_TIME clause in the table definition. This clause defines the name of the system-versioning period column, which is used to track when changes were made to the data.

Here’s an example of how to create a system-versioned temporal table:

1. Create a temporal table with an anonymous history table

				
					CREATE TABLE [dbo].[WeatherForecasts]
(
    [Id] INT NOT NULL PRIMARY KEY CLUSTERED,
[Date] [datetime2](7) NOT NULL,
[TemperatureC] [int] NOT NULL,
[Summary] [nvarchar](250) NOT NULL,
[CreatedAt] [datetime2](7) NOT NULL,
[CreatedBy] [nvarchar](max) NULL,
[LastModified] [datetime2](7) NULL,
[LastModifiedBy] [nvarchar](max) NULL,
[Location] [nvarchar](max) NULL,
    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

2. Or create a temporal table with a default history table

				
					CREATE TABLE [dbo].[WeatherForecasts1]
(
    [Id] INT NOT NULL PRIMARY KEY CLUSTERED,
[Date] [datetime2](7) NOT NULL,
[TemperatureC] [int] NOT NULL,
[Summary] [nvarchar](250) NOT NULL,
[CreatedAt] [datetime2](7) NOT NULL,
[CreatedBy] [nvarchar](max) NULL,
[LastModified] [datetime2](7) NULL,
[LastModifiedBy] [nvarchar](max) NULL,
[Location] [nvarchar](max) NULL,
    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 (HISTORY_TABLE = dbo.WeatherForecastsHistory));
GO

3. Or create a temporal table with a user-defined history table

				
					CREATE TABLE [dbo].[WeatherForecastsHistory](
[Id] [int] NOT NULL,
[Date] [datetime2](7) NOT NULL,
[TemperatureC] [int] NOT NULL,
[Summary] [nvarchar](250) NOT NULL,
[CreatedAt] [datetime2](7) NOT NULL,
[CreatedBy] [nvarchar](max) NULL,
[LastModified] [datetime2](7) NULL,
[LastModifiedBy] [nvarchar](max) NULL,
[Location] [nvarchar](max) NULL,
[ValidFrom] DATETIME2 NOT NULL,
[ValidTo] DATETIME2 NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE CLUSTERED COLUMNSTORE INDEX IX_WeatherForecastsHistory
    ON WeatherForecastsHistory;
CREATE NONCLUSTERED INDEX IX_WeatherForecastsHistory_Id_PERIOD_COLUMNS
    ON WeatherForecastsHistory (ValidTo, ValidFrom, Id);
GO
CREATE TABLE [dbo].[WeatherForecasts]
(
    [Id] INT NOT NULL PRIMARY KEY CLUSTERED,
[Date] [datetime2](7) NOT NULL,
[TemperatureC] [int] NOT NULL,
[Summary] [nvarchar](250) NOT NULL,
[CreatedAt] [datetime2](7) NOT NULL,
[CreatedBy] [nvarchar](max) NULL,
[LastModified] [datetime2](7) NULL,
[LastModifiedBy] [nvarchar](max) NULL,
[Location] [nvarchar](max) NULL,
    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 (HISTORY_TABLE = dbo.WeatherForecastsHistory));
GO

4. Altering an Existing Table to Support Temporal Data

				
					-- Existing Table
CREATE TABLE [dbo].[WeatherForecasts]
(
    [Id] INT NOT NULL PRIMARY KEY CLUSTERED,
[Date] [datetime2](7) NOT NULL,
[TemperatureC] [int] NOT NULL,
[Summary] [nvarchar](250) NOT NULL,
[CreatedAt] [datetime2](7) NOT NULL,
[CreatedBy] [nvarchar](max) NULL,
[LastModified] [datetime2](7) NULL,
[LastModifiedBy] [nvarchar](max) NULL,
[Location] [nvarchar](max) NULL
)
GO
-- Add columns
ALTER TABLE WeatherForecasts ADD  [ValidFrom] datetime2 NOT NULL;
ALTER TABLE WeatherForecasts ADD  [ValidTo] datetime2 NOT NULL;
GO
ALTER TABLE WeatherForecasts
ADD PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
ALTER TABLE WeatherForecasts
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WeatherForecastsHistory, DATA_CONSISTENCY_CHECK = ON));
GO

				
					ALTER TABLE dbo.WeatherForecasts ADD  
	ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(), 
	ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2), 
	PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo); 
  
ALTER TABLE dbo.WeatherForecasts 
	SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WeatherForecastsHistory)); 
GO

Now It’s time to update data to see the change history, We will consistently update the temperature of Dhaka for the date of 2023-05-09 from our application.

				
					SELECT Date, TemperatureC, CreatedAt, LastModified, Location, Summary, ValidFrom, ValidTo 
FROM WeatherForecasts
UNION
SELECT Date, TemperatureC, CreatedAt, LastModified, Location, Summary, ValidFrom, ValidTo  
FROM WeatherForecastsHistory
GO
DECLARE @StartDate DATETIME2 = '2023-05-09 08:54:23.2678482'
DECLARE @EndDate DATETIME2 = '9999-12-31 23:59:59.9999999' 
 
SELECT Date, TemperatureC, CreatedAt, LastModified, Location, Summary, ValidFrom, ValidTo 
FROM dbo.WeatherForecasts FOR SYSTEM_TIME BETWEEN @StartDate AND @EndDate
ORDER BY ValidFrom ASC
GO

Now we are able to see the change in history for a day. For example — From ‘2023-05-09 08:54:23.2678482’ to ‘2023-05-09 08:56:13.4474165’ the temperature of Dhaka was 32 C and last updated/current temperature is 38 C.

This is how we can track the changes and use that history for further analysis and reporting.