Temporal Tables: Keeping History Without a Trigger

SQL Server temporal tables keep earlier row versions automatically when system versioning is enabled. They help answer what the data looked like, but they do not automatically identify who changed it.

Understand Current Rows and History

A system-versioned table has a current table, a history table, and two period columns. Updates and deletes move previous row versions into history. SQL Server maintains the period values used to relate each version to system time.

The period timestamps use UTC and the beginning of the transaction. They are not necessarily the time of each statement or its commit. Business-effective dates remain a separate modeling question.

Use temporal history when you need retained row versions and time-based queries. Design a separate audit mechanism when actor identity or a business explanation is required. One feature does not automatically satisfy both requirements.

Create a Small Lab Table

Run this in a disposable user database where neither named table exists. The example creates persistent current and history tables. Keep each later step in the same query session as described.

CREATE TABLE dbo.TemporalPriceDemo
(
    ProductId int NOT NULL
        CONSTRAINT PK_TemporalPriceDemo PRIMARY KEY,
    Price decimal(10,2) NOT NULL,
    ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
    SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = dbo.TemporalPriceDemoHistory)
);
INSERT dbo.TemporalPriceDemo (ProductId, Price) VALUES (1, 10.00);

The period columns are maintained by the engine. The current table needs its own primary key. Review documented schema and history-table restrictions before adapting the pattern to an existing application.

Create a Distinct Earlier State

For this demonstration, use autocommit with no surrounding transaction and implicit transactions disabled. The wait separates the observation time from the update transaction. It is a teaching aid rather than an application design.

DECLARE @BeforeUpdate datetime2(7) = SYSUTCDATETIME();
WAITFOR DELAY '00:00:01';
UPDATE dbo.TemporalPriceDemo
SET Price = 12.00
WHERE ProductId = 1;
SELECT ProductId, Price, ValidFrom, ValidTo
FROM dbo.TemporalPriceDemo
FOR SYSTEM_TIME AS OF @BeforeUpdate;
SELECT ProductId, Price, ValidFrom, ValidTo
FROM dbo.TemporalPriceDemo;

The first query asks for the row version valid at the captured UTC time. The second queries the current table. Compare the two after execution instead of assuming that current and historical reads are interchangeable.

Multiple changes within one transaction share its beginning timestamp. Some zero-duration versions are excluded by temporal query clauses. Understand that behavior before expecting every intermediate statement to appear in an AS OF result.

Inspect History and Metadata

SELECT ProductId, Price, ValidFrom, ValidTo
FROM dbo.TemporalPriceDemo
FOR SYSTEM_TIME ALL
ORDER BY ProductId, ValidFrom;
SELECT name, temporal_type_desc,
       OBJECT_SCHEMA_NAME(history_table_id) AS history_schema,
       OBJECT_NAME(history_table_id) AS history_table
FROM sys.tables
WHERE object_id = OBJECT_ID(N'dbo.TemporalPriceDemo');

FOR SYSTEM_TIME ALL combines applicable current and historical versions for analysis. Querying the history table directly answers a different storage-level question. Choose the query form according to the evidence you need.

When adding an alias, place the FOR SYSTEM_TIME clause before the alias. Use a consistent UTC interpretation for supplied timestamps. Converting local business times requires explicit handling of the relevant time zone.

Plan the Cost of Retention

Frequent updates can create substantial history even when the current table remains small. Retention, indexing, and maintenance need a deliberate policy. Keeping every version forever is an operating choice with storage and query costs.

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
       OBJECT_NAME(p.object_id) AS table_name,
       SUM(CASE WHEN p.index_id IN (0,1)
                THEN p.row_count ELSE 0 END) AS approximate_rows,
       SUM(p.reserved_page_count) * 8.0 / 1024 AS reserved_mb
FROM sys.dm_db_partition_stats AS p
WHERE p.object_id IN
(OBJECT_ID(N'dbo.TemporalPriceDemo'),
 OBJECT_ID(N'dbo.TemporalPriceDemoHistory'))
GROUP BY p.object_id;

Track current and history storage separately over representative activity. A quiet lab example cannot estimate production growth. Test historical query plans with the retention volume you expect to keep.

Review supported retention and partition-management options for the installed version. Preserve required evidence before changing a retention policy. Normal backup and recovery planning still applies to the database.

Keep the Audit Question Separate

Temporal history records row values and system-time validity, not a trusted human identity by default. Shared application accounts make attribution especially different from version storage. Add appropriate application context or auditing when the requirement includes who and why.

History is also subject to administrative control and documented maintenance operations. Do not present temporal tables alone as a tamper-proof compliance archive. Match the complete design to the evidence and retention requirements.

Test insertion, update, deletion, and historical queries before adoption. Confirm how the application interprets time and how operators manage growth. The feature works best when its history semantics are understood before an investigation begins.

Temporal history is not a complete audit trail, it is a maintained record of row versions through system time.

This post was rewritten from scratch in September 2026. The original, published on 2011-10-05, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.