When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?

Question

When DML and DDL queries are executed against a Temporal Table, those changes are appropriately propagated to the correlating History table. E.g. an UPDATE to the Temporal Table results in a new record to be INSERTed into the History table to reflect that change.

Is that INSERT to the History table, as well as other propagated changes, logged in the Transaction Log?

Does that mean enabling Temporal Tables will effectively double the amount of data that goes through the Transaction Log (at least for a database with the Recovery Model set to Full)?

Answer 1

Yes, changes to the history table get logged.

If you think about the purpose of the transaction log, it's to allow for recovery (via backups and restores, or the normal recovery process that occurs on database startup). If changes to the history table were not logged, then restoring a backup of a database with temporal tables would likely result in inconsistencies between the main table and the history table.

Here's a small demo. The setup creates a temporal table, inserts a row, updates that row, and then clears the log.

USE [master];
GO

DROP DATABASE IF EXISTS [315097];
GO

CREATE DATABASE [315097];
ALTER DATABASE [315097] SET RECOVERY SIMPLE 
GO

USE [315097];
GO

CREATE TABLE dbo.Employee
(
    EmployeeID int NOT NULL, 
    [Name] nvarchar(100) NOT NULL, 
    Position varchar(100) NOT NULL, 
    Department varchar(100) NOT NULL, 
    [Address] nvarchar(1024) NOT NULL, 
    AnnualSalary decimal (10,2) NOT NULL, 
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START, 
    ValidTo datetime2 GENERATED ALWAYS AS ROW END,

    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
)
WITH 
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory)
);
GO

/*
INSERT an initial row to avoid a lot of "noise" in this test related to inserting
the very first row into these tables
*/
INSERT INTO dbo.Employee
    (EmployeeID, [Name], Position, Department, [Address], AnnualSalary)
VALUES
    (1, N'Josh', 'Trusted User', 'I.T.', N'123 Main St', 3.50);

UPDATE dbo.Employee
SET AnnualSalary = 4.00;
GO

/*
Clear the log
*/
BACKUP DATABASE [315097] TO DISK = N'NUL';
GO
CHECKPOINT;
GO
/*
Query showing only checkpoints
*/
SELECT 
    l.[Current LSN],
    l.Operation,
    l.Context,
    l.AllocUnitName
FROM fn_dblog(NULL, NULL) l;

Now if I update a row in the table and then run the same query against the log:

UPDATE dbo.Employee
SET AnnualSalary = 3.50;
GO

Those 6 new log records are:

• 2 for the begin / end of the implicit transaction
• 2 for updating the differential backup bitmap
• 1 for the update to the base table
• 1 for the "hidden" insert into the history table

If I change the CREATE TABLE statement to remove the system versioning related items, and re-run the demo, the final state of the log looks like this:

Does that mean enabling Temporal Tables will effectively double the amount of data that goes through the Transaction Log

It won't exactly be double (inserts into the main table don't make any changes to the history table), but yes there will be an increase in log throughput for sure.