Infrastructure at your Service

Stéphane Haby

SQL Server Temporal Table – How to store a history table in another file?

Few days ago, a customer asks me if it is possible to move the history table to slower but cheaper storage.
The question behind this is whether it is possible to create a history table on a separate filegroup and file.
Few years ago, I write a serie of blogs about temporal table here.

I will take the same example to try to set up a filegroup specific to a history table.
In my sample, I create a ‘Zoo’ database with a table ‘Animals’ which inventory animals.

First, I create the filegroup HISTORY and add a file Zoo_history.ndf:

USE [master]
GO
ALTER DATABASE [Zoo] ADD FILEGROUP [HISTORY]
GO
ALTER DATABASE [Zoo] ADD FILE ( NAME = N'Zoo_History', FILENAME = N'D:\DATA\Zoo_History.ndf' , SIZE = 131072KB , FILEGROWTH = 131072KB )
 TO FILEGROUP [HISTORY]
GO

Before I create the table Animals, I create the history table [AnimalsHistory] on this filegroup [HISTORY] with also a separate schema [History] (it’s a good practice):

USE [Zoo]
GO
CREATE SCHEMA [History] AUTHORIZATION [dbo]
GO
CREATE TABLE [History].[AnimalsHistory]
(
 [AnimalId] [int]  NOT NULL,
 [Name] [varchar](200) NOT NULL,
 [Genus Species] [varchar](200) NOT NULL,
 [Number]  [int] NOT NULL,
 [StartDate] [datetime2]  NOT NULL,
 [EndDate]  [datetime2] NOT NULL,

) ON [HISTORY]

history_table01

At this time, the table is not a history table. It will be after the creation of the principal table:

CREATE TABLE [dbo].[Animals]
(
 [AnimalId] [int]  NOT NULL,
 [Name] [varchar](200) NOT NULL,
 [Genus Species] [varchar](200) NOT NULL,
 [Number]  [int] NOT NULL,
  CONSTRAINT [PK_Animals] PRIMARY KEY CLUSTERED ([AnimalId] ASC),
  /*Temporal: Define the Period*/
  [StartDate] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
  [EndDate]  [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 PERIOD FOR SYSTEM_TIME([StartDate],[EndDate])
) 
 WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE = [History].[AnimalsHistory]))

history_table02

Now the history table is link to my table System-Versioned with a separate file.
I run few queries to have data and as you can see, all previous version of each updated row is inserted into the history table in the HISTORY filegroup.

INSERT INTO [Zoo].[dbo].[Animals]([AnimalId],[Name],[Genus Species],[Number])
     VALUES(1,'African wild cat','Felis silvestris lybica',10)
GO

UPDATE [Zoo].[dbo].[Animals] SET Number = 21 WHERE Name = 'African wild cat' AND  [Genus Species]= 'Felis silvestris lybica';
GO
UPDATE [Zoo].[dbo].[Animals] SET Number = 5 WHERE Name = 'African wild cat' AND  [Genus Species]= 'Felis silvestris lybica';
GO
UPDATE [Zoo].[dbo].[Animals] SET Number = 12 WHERE Name = 'African wild cat' AND  [Genus Species]= 'Felis silvestris lybica';
GO
UPDATE [Zoo].[dbo].[Animals] SET Number = 20 WHERE Name = 'African wild cat' AND  [Genus Species]= 'Felis silvestris lybica';
GO
...

As you can see below, all changes are in the filegroup HISTORY:

history_table03

I recommend creating a separate filegroup for history table in case of temporal table usage.
It is easier to manage (table growth), will not be place in your “principal” data file and can be place on a different storage if needed.
I hope this will help you to design your database

2 Comments

  • Naresh Obula says:

    Hi
    Thanks for the article.
    I am looking for a way to save the history table data on a different filegroup other than the primary filegroup “without defining the schema for history table”. Any easy way to store history tables data either on the filegroup where its primary table is, or to any different filegroup, mentioning again, “without defining the schema for history table”.
    Ex. I have nearly 500 tables that are part of SYSTEM_VERSIONING (the tables schema is maintained in a visual studio Project), but all the history data is saved on to Primary Filegroup. In this case, I don’t want to define schema for another 500 history tables to make sure they stay on a different filegroup everytime I deploy the database. Is there any way?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant