Temporal tables, historical retention, and lifecycle management

Overview

Temporal tables, also called system-versioned temporal tables, are SQL Server tables that automatically keep historical row versions. A temporal table has a current table, a history table, and period columns that describe when each row version was valid. SQL Server maintains the history when rows are updated or deleted.

This topic matters because many systems need to answer time-based questions: what did this customer record look like last week, who changed a value, which rows were active during a period, or how can we recover from an accidental update? Temporal tables provide built-in point-in-time data history, but they also introduce storage growth, indexing, retention, security, and lifecycle-management responsibilities.

For interviews, this topic tests whether you can explain temporal table mechanics and also the operational trade-offs. Strong candidates can describe current and history tables, period columns, FOR SYSTEM_TIME, retention policies, partitioning, cleanup, indexing, and when temporal history is not the same as business event sourcing or backups.

Core Concepts

What A Temporal Table Is

A system-versioned temporal table stores current rows in the main table and previous row versions in a linked history table.

Example:

CREATE TABLE dbo.Employee
(
    EmployeeId int NOT NULL PRIMARY KEY,
    Name nvarchar(100) NOT NULL,
    Department nvarchar(100) NOT NULL,
    Salary decimal(12, 2) NOT NULL,
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.EmployeeHistory
    )
);

SQL Server manages the validity period and moves old versions to the history table during updates and deletes.

Current Table And History Table

A temporal table uses two related tables:

• Current table: stores the latest version of each row.
• History table: stores previous versions of rows.

When a row is updated, SQL Server writes the old version to history and updates the current row. When a row is deleted, SQL Server writes the old version to history and removes it from the current table.

Inserts create only current rows. There is no previous history version for a new row.

Period Columns

Temporal tables require two datetime2 period columns:

• Start column, often named ValidFrom.
• End column, often named ValidTo.

The period columns define when a row version was valid from the database system's perspective. The system uses UTC transaction begin time for these values.

Period columns can be visible or hidden. Hidden period columns are not returned by SELECT *, but they can still be queried explicitly.

System Versioning

System versioning is enabled with SYSTEM_VERSIONING = ON. When enabled, SQL Server automatically records history for updates and deletes.

To perform certain schema changes, maintenance tasks, or direct history cleanup approaches, you may need to turn system versioning off temporarily. That should be done carefully because changes made while versioning is off are not automatically captured.

Querying Temporal Data

Temporal queries use FOR SYSTEM_TIME.

Point-in-time query:

SELECT EmployeeId, Name, Department, Salary
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2026-06-20T10:00:00'
WHERE EmployeeId = 100;

All history:

SELECT EmployeeId, Name, Department, Salary, ValidFrom, ValidTo
FROM dbo.Employee
FOR SYSTEM_TIME ALL
WHERE EmployeeId = 100
ORDER BY ValidFrom;

Common forms include AS OF, FROM ... TO, BETWEEN ... AND, CONTAINED IN, and ALL.

AS OF Queries

AS OF returns rows that were valid at a specific point in time.

SELECT *
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2026-01-01T00:00:00'
WHERE Department = 'Finance';

This is useful for reconstructing prior state. It is not the same as event history. It shows row versions, not the business reason behind changes.

Temporal Tables Vs Audit Tables

Temporal tables automatically store old row versions. Audit tables usually store who changed data, why, where the change came from, and business context.

Temporal tables answer:

• What did the row look like at a time?
• Which row versions existed?
• When was this database version valid?

Audit/event systems answer:

• Who changed it?
• Why was it changed?
• What business event caused it?
• Which application or workflow performed it?

Temporal tables are not a full audit solution by themselves.

Temporal Tables Vs Backups

Temporal tables do not replace backups. Temporal history lives in the database and can be affected by deletion, permission mistakes, corruption, ransomware, or retention cleanup. Backups provide independent recovery points.

Temporal tables are useful for row history and point-in-time querying. Backups are required for disaster recovery and database-level recovery.

Retention Requirements

Historical data grows over time. Retention should be intentional.

Retention decisions depend on:

• Legal requirements.
• Business audit requirements.
• Privacy requirements.
• Storage cost.
• Query performance.
• Recovery needs.
• Data lifecycle policies.

Keeping every historical version forever may be expensive and legally undesirable.

Retention Cleanup

SQL Server supports approaches for managing temporal history retention, including built-in retention features in supported environments and manual cleanup patterns. Large history cleanup should be planned carefully.

Cleanup concerns include:

• Long-running deletes.
• Transaction log growth.
• Blocking.
• Index maintenance.
• Partition switching.
• Compliance requirements.
• Accidentally deleting required history.

For large tables, partitioning history by time can make lifecycle management more predictable.

Partitioning History Tables

History tables often grow quickly. Partitioning by period end time can help manage retention and large history volumes.

Benefits include:

• Faster archival or deletion by time range.
• Better manageability for large history.
• Potentially easier maintenance windows.
• Alignment with retention policy.

Partitioning adds complexity and should be justified by data volume and operational needs.

Indexing Temporal Tables

Temporal queries need indexes on both current and history tables. Common access patterns include:

• Lookup by primary key and time.
• Time range queries.
• Entity history queries.
• Reporting queries over historical periods.

Useful index patterns often include the business key and period columns.

CREATE INDEX IX_EmployeeHistory_EmployeeId_ValidTo_ValidFrom
ON dbo.EmployeeHistory (EmployeeId, ValidTo, ValidFrom);

Index choices should follow query patterns and retention operations.

Schema Changes

Temporal tables can be altered, but some schema changes are more constrained because current and history tables must remain compatible. Adding columns, changing data types, or modifying period columns requires careful planning.

For release safety:

• Test temporal schema migrations in staging.
• Understand whether system versioning must be turned off.
• Preserve history table compatibility.
• Avoid losing history unintentionally.
• Include history table indexes and retention changes in review.

Common Mistakes

Common mistakes include:

• Assuming temporal tables are full audit logs.
• Assuming temporal history replaces backups.
• Forgetting history storage growth.
• Querying all history without time filters.
• Missing indexes on history tables.
• Not defining retention policy.
• Turning system versioning off and forgetting to turn it back on.
• Deleting history without legal or business approval.
• Ignoring security on the history table.
• Confusing system time with business effective time.

Best Practices

Best practices include:

• Use temporal tables for row-version history and point-in-time reconstruction.
• Use audit or event tables when you need actor and business context.
• Keep backups regardless of temporal history.
• Define retention before history grows uncontrollably.
• Index history tables for expected temporal queries.
• Consider partitioning for large history tables.
• Secure current and history data consistently.
• Test schema changes and retention cleanup.
• Use UTC consistently when querying temporal periods.
• Monitor storage growth and query performance.