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]

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]))

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:

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