SQL Temporal Tables for Changes, Compression and Indexing

By: Ameena Lalani | Comments | Related: 1 | 2 | 3 | 4 | > Temporal Tables

Problem

In part 1 and part 2 of this tip series we learned about various benefits of Temporal Tables. In this tip, we look at some additional benefits like schema changes, compression and indexing.

Solution

In part 1 of this series, we discussed the benefits of using Temporal Tables in recovering data from updates and deletes. In part 2, we discussed various ways in which a temporal history table can be secured from unwanted data access. Temporal tables are useful in applications where tracking of data changes is required. Let’s look at another example to understand other benefits of using these special tables. In this tip we will go through various examples and see other benefits such as ease of coding, built in optimization and low maintenance that comes with implementation of Temporal Tables.

Changing temporal table schema with ALTER Column

You can use the ALTER TABLE command to make schema changes on Temporal Tables. Changes made to the temporal table will propagate to the history table without issuing any additional commands. Using ALTER TABLE, you can add or remove a column or you can change the data type of a column without turning system_versioning = OFF. To run this command, a user will need “CONTROL” permission in the database.

Let me walk you through an example for better understanding of this feature.

-- Create database
USE master;
GO
 
DROP DATABASE IF EXISTS TemporalDB;
GO
CREATE DATABASE TemporalDB;
GO
 
-- Create table
USE TemporalDB;
GO

CREATE TABLE Customer(
   Id INT IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY,
   CustomerName VARCHAR(50) NOT NULL ,
   StartDate DATETIME2 GENERATED ALWAYS AS ROW START  NOT NULL, 
   EndDate DATETIME2 GENERATED ALWAYS AS ROW END  NOT NULL,
   PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory)) 
GO
 
-- Scenario 1: Alter data type of column in temporal table
ALTER TABLE Customer ALTER COLUMN CustomerName NVARCHAR(200);
 
-- Insert data
INSERT INTO Customer (CustomerName)
SELECT 'Might Mouse' UNION SELECT  'Mickey Mouse' UNION SELECT 'Charlie Brown';
 
SELECT * FROM dbo.Customer

In the screenshots below you will see the before and after data type changes in the temporal and history table. Before the CustomerName column was varchar(50) in both tables. We issued the command to alter the data type in temporal table only, but in the after screenshot we see both columns data type has been changed to varchar(200). The benefit here is that you only have to issue the ALTER command one time and the changes take place in both tables. You do not have to remember, like in manual solutions, to make the same changes to history table and the main table.

BEFORE

AFTER

This is the data so far in Customer table.

Be aware that an ALTER TABLE operation holds a schema lock on both tables.

Changing temporal table schema with ADD Column

Now we will see how adding a column works. We will add a column called City to our Customer table. Then we will insert a row into the Customer table.

-- Scenario 2: Lets add a column. Since table already has data, we need to define default on that column 
ALTER TABLE dbo.Customer ADD City VARCHAR(20) NOT NULL CONSTRAINT DF_City DEFAULT 'Happyville' ; 
  
INSERT INTO dbo.Customer (CustomerName) 
SELECT 'Ameena Lalani' 
  
SELECT * FROM dbo.Customer;

As we see in the below screenshot, a new column “City” has been added to both the Temporal and the history tables. Again, less code to write. Please observe the default constraints on both the tables. The constraint DF_City only got created on the Temporal table and this is because the default is fired only when data is inserted and for history table no insert is allowed. This is a very smart and was thought through by the Microsoft team.

Be aware that if an existing non-temporal history table has a default value for a NOT NULL column, remove it before adding it to the temporal table because for the history table all columns are automatically populated by the system.

This is the data now in Customer Table.

Note About Some Schema Changes

For some schema ALTER operations, you need to set System_Versioning = OFF first. Such as when adding an identity column or a computed column.

Index Optimization on History Table

When we created the Customer table, a clustered index was created on the history table on the “EndDate” and “StartDate” columns automatically. This is a built-in optimization feature and it works with various “FOR SYSTEM_TIME” query sub clauses. This benefit is only available when a history table is created at the same time of the temporal table creation. If there is an existing history table that you want to use with a temporal table, you have to manually create the indexes yourself.

Data Compression

When a history table is created with the creation of the temporal table, meaning it is not an existing history table, then SQL Server does yet one more optimization. It makes the history table pages compressed in anticipation that the history table is going to grow, depending on the DML activities in that table. Again, if you have existing history table, you can alter it to add the page compression property manually and then attach it with the temporal table.

Impact of Page Compression

Let’s create another history table called “ManualCustomerHistory”. We will update all rows in this temporal table which will add the same number of rows in the history table. We will insert the same rows in the ManualCustomerHistory table and then we will compare the data size of both tables.

-- Create a database
USE master;
GO
 
DROP DATABASE IF EXISTS TemporalDB;
GO
CREATE DATABASE TemporalDB;
GO
 
-- Create a temporal table
USE TemporalDB;
GO

CREATE TABLE dbo.Customer(
   Id INT IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY,
   CustomerName VARCHAR(50) NOT NULL ,
   StartDate DATETIME2 GENERATED ALWAYS AS ROW START  NOT NULL, 
   EndDate DATETIME2 GENERATED ALWAYS AS ROW END  NOT NULL,
   PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory)) 
GO
 
-- Insert data from WideWorldImporters
INSERT INTO TemporalDB.dbo.Customer (CustomerName)
SELECT Fullname FROM WideWorldImporters.Application.People
 
-- Update  Customer table
-- This query is without a where clause on purpose so that all names will be updated to this one name.
UPDATE dbo.Customer SET CustomerName = 'Ameena Lalani' 
GO
 
-- Create a separate ManualCustomerHistory table. This is not a temporal table but only a regular table with historical data
USE TemporalDB;
GO
 
CREATE TABLE ManualCustomerHistory (
   Id INT IDENTITY (1,1) NOT NULL ,
   CustomerName VARCHAR(50) NOT NULL ,
   StartDate DATETIME2   NOT NULL DEFAULT GETDATE(), 
   EndDate DATETIME2   NOT NULL DEFAULT GETDATE()+1,
)
GO

-- Insert  rows in ManualCustomerHistory table from CustomerHistory table so both tables now have same data.
INSERT INTO dbo.ManualCustomerHistory (CustomerName) 
SELECT CustomerName FROM dbo.CustomerHistory

The goal of the above exercise is to show that although both tables have the same number of rows, the same data, the same column data type and the same column size, you will still observe the difference in data size between these 2 tables. Even though ManaulCustomerHistory table has no index, its data size is still bigger than the CustomerHistory table which is part of the temporal table. The secret is the Page compression optimization which the CustomerHistory table gets when it is created with the temporal table. This data compression is the reason the data size is smaller for the CustomerHistory table.

-- Now verify rows in both history table
SELECT COUNT(*) AS CustomerHistoryCount FROM dbo.CustomerHistory
SELECT COUNT(*) AS ManualCustomerHistoryCount FROM dbo.ManualCustomerHistory
GO

-- Look at the data size of both tables 
-- this has compressed pages
EXECUTE sp_spaceused 'CustomerHistory'; 
GO 
-- this does not have compressed pages
EXECUTE sp_spaceused 'ManualCustomerHistory'; 
GO

Summary

SQL Server provides many built-in optimizations for Temporal Tables that are not available if you create a manual data tracking system. When you alter a column data type or size for a temporal table, the history table automatically gets the update. If you add a column to a temporal table, the history table also gets the column without writing additional code. History table storage is also optimized by making the history table page compressed.

Next Steps

Read this Microsoft documentation on Changing Schema of a System Versioned Temporal Table.
Another useful document to read is about modifying data in temporal tables.
Check out the prior tips in the series:
- Benefits of using SQL Server Temporal Tables - Part 1
- Security Benefits for SQL Server Temporal Tables - Part 2
Check out all of the SQL Server Temporal Table tips.

About the author

Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Benefits of using SQL Server Temporal Tables to Propagate Changes, Compression and Indexing - Part 3