Sql-server – SQL Server Temporal Tables History Table size

Or does it do some kind of compression under the hood to realize that most of the data did not change and does not need to be stored each time?

By default, the temporal table's history table is PAGE compressed.

Source

To ask it I am going to create a hypothetical situation. Say I have a table with 300 columns in it. Each column has a char(10) in it. (3,000 bytes).

I insert a row into the main table.

I then go an modify one of the columns with an update statement. That causes the modified row to be inserted into the history table.

I then change that same column 4 more times (5 modifications total.)

What I am wondering is if that means that the size of my history table is now 3,000 x 5 = 15,000 bytes?

Testing

Here is some pseudo code to create the table & add 300 char(10) columns

CREATE TABLE dbo.BigTable (id int identity(1,1)  primary key not null   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON)   
;  

To ask it I am going to create a hypothetical situation. Say I have a table with 300 columns in it. Each column has a char(10) in it. (3,000 bytes).

SELECT
TOP(300)
 'ALTER TABLE dbo.BigTable ADD COLUMN'+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(50))+ ' char(10)'
FROM master..spt_values;

I insert a row into the main table.

INSERT INTO dbo.BigTable
(
Columns 1 Until 300
)
VALUES
('bla', * 300);

I then go an modify one of the columns with an update statement. That causes the modified row to be inserted into the history table. I then change that same column 4 more times (5 modifications total.)

UPDATE dbo.BigTable 
SET [COLUMN7] = 'blabla';
GO 5

What I am wondering is if that means that the size of my history table is now 3,000 x 5 = 15,000 bytes?

Well, when using the query from this source

This is the result:

SchemaName  TableName   RowCounts   Used_MB Unused_MB   Total_MB
dbo MSSQL_TemporalHistoryFor_2002106173 5   0.02    0.05    0.07

We get about 0.02 MB used for this history table, which is more than the 0.015mb specified.

However when we check SSMS, the details are lower.

Only 0.008 MB?

A simplified version of the query being run is this one:

DECLARE 
@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)
SELECT @_msparam_0=N'1',@_msparam_1=N'E',@_msparam_2=N'MSSQL_TemporalHistoryFor_2002106173',@_msparam_3=N'dbo'
        declare @PageSize float
        select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1


SELECT         ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
        FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        where i.object_id = tbl.object_id),0.0)
FROM
sys.tables AS tbl
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
OPTION (FORCE ORDER)

Even more simplified, we see that the history table's clustered index data is only one data page;

SELECT  i.name, i.index_id,a.data_pages,i.type_desc
  FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE i.name = 'ix_MSSQL_TemporalHistoryFor_2002106173'

Result

name    index_id    data_pages  type_desc
ix_MSSQL_TemporalHistoryFor_2002106173  1   1   CLUSTERED

Why the difference?

This is due to the ssms query only considering data_pages. This differs from used_pages

Consider this query:

SELECT  i.name, i.index_id,a.data_pages,a.used_pages,a.total_pages, i.type_desc
  FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE i.name = 'ix_MSSQL_TemporalHistoryFor_2002106173'


name    index_id    data_pages  used_pages  total_pages type_desc
ix_MSSQL_TemporalHistoryFor_2002106173  1   1   2   9   CLUSTERED

Not all used_pages will be data_pages, while all data_pages will be used_pages.

There are overhead pages like PFS, GAM, SGAM, HEADER PAGE etc.

Summary

In our example, due to the PAGE compression, the data stored in the history table is in the 8KB range. This results in one data page used.

The SSMS Query only considers data_pages, while the other query takes into account all used_pages.

For each update, an additional row is stored in the history table, without (page) compression, the data pages would amount to 2 (and be above or equal to 15k bytes).