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