How to Use SQL Server Temporal Tables to Track Changes to Your Data

Applications often need to show what a database record looked like before it was updated or deleted. For example, a finance application may need to review an account's previous balance, an HR system may need to check an employee's earlier department, or an order management system may need to investigate when an order's status changed.

Without historical data, developers usually have to build custom audit tables, write triggers, or store previous values in application code. These approaches can work, but they add maintenance overhead and create opportunities for missing records.

SQL Server provides a built-in feature called system-versioned temporal tables. It automatically maintains historical versions of rows when data is updated or deleted. Developers can then query the current table or retrieve the data as it existed at a particular point in time.

In this article, we will create a temporal table, track changes automatically, query historical records, and discuss the practical considerations of using temporal tables in production applications.

What Are SQL Server Temporal Tables?

A temporal table stores information about how rows change over time. SQL Server maintains two tables:

Current table: Stores the latest version of each row.
History table: Stores previous versions of rows that have been updated or deleted.

SQL Server manages the movement of row versions between these tables. When an update changes a row, the previous version is copied to the history table, and the current table receives the new values. When a row is deleted, its last version is retained in the history table.

Each version has two period columns:

ValidFrom: The start of the period during which the row version is considered valid.
ValidTo: The end of that period.

SQL Server uses these columns to support historical queries. The period values are based on the UTC transaction begin time, rather than the exact time each individual statement finishes.

A temporal table is useful when you need to answer questions such as:

What was this customer's address last month?
What was the price of a product before its latest update?
Which department was an employee assigned to at a specific time?
What did an order record look like before it was deleted?

How Temporal Tables Work

Consider an Employees table containing employee details. Initially, an employee belongs to the Sales department. Later, the employee moves to Engineering.

With a regular table, the update replaces the old department value. Unless another system records the change, the previous value is lost.

With a system-versioned temporal table, SQL Server preserves the previous row version in the history table.

Operation	Current table	History table
Insert a new employee	Stores the new row	No previous version
Update employee details	Stores the updated row	Stores the previous version
Delete an employee	Row is removed	Stores the final version
Query a historical point in time	Returns the applicable version when queried temporally	Supplies older versions

The application does not need to manually insert old values into the history table. SQL Server handles this automatically while maintaining the temporal table's version history.

Step 1: Create a System-Versioned Temporal Table

Let's create an employee table that tracks changes to an employee's name, department, and salary.

The table needs two period columns and a PERIOD FOR SYSTEM_TIME definition. We will also let SQL Server create the history table automatically.

CREATE TABLE dbo.Employees
(
    EmployeeId INT NOT NULL PRIMARY KEY,
    EmployeeName 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.EmployeesHistory
    )
);

Here is what the important parts do:

GENERATED ALWAYS AS ROW START defines the beginning of the row's system-time period.
GENERATED ALWAYS AS ROW END defines the end of that period.
PERIOD FOR SYSTEM_TIME identifies the two columns SQL Server uses for temporal queries.
SYSTEM_VERSIONING = ON enables automatic history management.
HISTORY_TABLE specifies the name of the history table SQL Server should create.

The history table stores previous row versions, including the period values needed to query them.

SQL Server normally assigns a start time to the current row and an end time far in the future. When the row changes, SQL Server closes the old version's period and starts a new version.

You can inspect the current and history tables separately:

SELECT *
FROM dbo.Employees;

SELECT *
FROM dbo.EmployeesHistory;

The second query shows the historical rows currently stored in the history table. It does not include the latest version of each employee.

Step 2: Insert and Update Data

Insert an employee into the temporal table.

INSERT INTO dbo.Employees
(
    EmployeeId,
    EmployeeName,
    Department,
    Salary
)
VALUES
(
    101,
    N'Ananya Sharma',
    N'Sales',
    65000.00
);

At this point, the employee exists in the current table. There is no earlier version to retain in the history table.

Now update the employee's department and salary.

UPDATE dbo.Employees
SET
    Department = N'Engineering',
    Salary = 78000.00
WHERE EmployeeId = 101;

SQL Server automatically stores the previous version in the history table before retaining the updated version in the current table.

You can inspect the historical data with:

SELECT
    EmployeeId,
    EmployeeName,
    Department,
    Salary,
    ValidFrom,
    ValidTo
FROM dbo.EmployeesHistory
WHERE EmployeeId = 101;

The history table should contain the earlier Sales department and salary values.

If you update the employee again, SQL Server retains another previous version. Over time, the history table becomes a sequence of row versions.

A temporal table tracks changes to rows, not just changes to individual columns. Even when only one column changes, the historical version contains the row's values as they existed before that update.

Step 3: Query Data as It Existed at a Specific Time

One of the most useful features of temporal tables is the FOR SYSTEM_TIME clause.

Instead of manually combining the current and history tables, you can ask SQL Server to return the row version applicable at a particular point in time.

For example:

DECLARE @AsOfTime DATETIME2 =
    '2026-10-01T10:00:00';

SELECT
    EmployeeId,
    EmployeeName,
    Department,
    Salary
FROM dbo.Employees
FOR SYSTEM_TIME AS OF @AsOfTime
WHERE EmployeeId = 101;

SQL Server checks both the current table and the history table and returns the version that was valid at the specified system time.

If the employee belonged to Sales at that time, the query returns the Sales version. If the employee had already moved to Engineering, it returns the Engineering version.

The example timestamp is illustrative. Use a timestamp that falls within the period you want to investigate.

The AS OF clause is particularly useful for investigating historical business records, reconstructing previous application states, and comparing historical values with current data.

Step 4: Retrieve Changes Across a Time Range

Sometimes you need more than one historical snapshot. You may want to see the versions that existed during a period.

SQL Server supports several temporal query options.

Use FROM ... TO

FROM ... TO returns row versions whose periods overlap the requested interval, excluding versions that begin exactly at the interval's end.

SELECT
    EmployeeId,
    EmployeeName,
    Department,
    Salary,
    ValidFrom,
    ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME FROM
    '2026-10-01T00:00:00'
TO
    '2026-10-16T00:00:00'
WHERE EmployeeId = 101
ORDER BY ValidFrom;

This is useful when you want to review versions that were active at some point during a time window.

Use BETWEEN ... AND

BETWEEN ... AND includes versions that were active at the specified endpoints, subject to temporal period boundary rules.

SELECT
    EmployeeId,
    Department,
    Salary,
    ValidFrom,
    ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME BETWEEN
    '2026-10-01T00:00:00'
AND
    '2026-10-16T00:00:00'
WHERE EmployeeId = 101
ORDER BY ValidFrom;

Use this when the endpoint behavior is appropriate for the report you are building. For time windows that must not overlap, FROM ... TO is often easier to reason about.

Use CONTAINED IN

CONTAINED IN returns versions whose entire system-time periods are contained within the specified interval.

SELECT
    EmployeeId,
    Department,
    Salary,
    ValidFrom,
    ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME CONTAINED IN
(
    '2026-10-01T00:00:00',
    '2026-10-16T00:00:00'
)
WHERE EmployeeId = 101;

This is different from returning every version that merely overlaps the interval. A version extending beyond the requested boundaries is not fully contained within that range.

Use ALL

ALL returns the current and historical versions together.

SELECT
    EmployeeId,
    EmployeeName,
    Department,
    Salary,
    ValidFrom,
    ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeId = 101
ORDER BY ValidFrom;

This query is useful when you need a complete version timeline for a particular employee.

Step 5: Track Deleted Records

Temporal tables also preserve the final version of a row when it is deleted.

For example:

DELETE FROM dbo.Employees
WHERE EmployeeId = 101;

The employee is removed from the current table, but the last row version remains in the history table.

You can retrieve the deleted employee's previous data using a temporal query:

SELECT
    EmployeeId,
    EmployeeName,
    Department,
    Salary,
    ValidFrom,
    ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeId = 101
ORDER BY ValidFrom;

The query returns the versions that remain available in the current and history tables. Since the current row has been deleted, only its historical versions should remain.

This is useful when users accidentally delete business records and support teams need to inspect the previous values.

However, a temporal table does not automatically restore deleted rows. To recover a record, you must use the historical data to construct and execute an appropriate insert or recovery operation.

Step 6: Query Temporal Data Across Related Tables

Temporal tables become especially useful when several related tables have their own history.

Suppose both Employees and Departments are system-versioned temporal tables. You can query both tables at the same point in time to reconstruct a historical view of the relationship.

DECLARE @AsOfTime DATETIME2 =
    '2026-10-01T10:00:00';

SELECT
    e.EmployeeId,
    e.EmployeeName,
    e.Department AS EmployeeDepartment,
    d.DepartmentName,
    d.DepartmentLocation
FROM dbo.Employees
FOR SYSTEM_TIME AS OF @AsOfTime AS e
INNER JOIN dbo.Departments
FOR SYSTEM_TIME AS OF @AsOfTime AS d
    ON e.Department = d.DepartmentName;

Each temporal table is evaluated at the same requested system time. This can help reconstruct the database state for historical reporting.

The example assumes the employee's department is matched using its name. In a production database, joining through a stable DepartmentId foreign key is generally safer than using department names.

There is also an important distinction between temporal consistency and business consistency. Temporal queries reconstruct system-time versions, but they do not guarantee that every related table has a complete business audit trail or that all business operations were valid.

Step 7: Enable Temporal Versioning on an Existing Table

You do not always need to create a new table to use temporal functionality. An existing table can be adapted, but the process requires careful planning.

For example, an existing table may already have a primary key and application data. You can add period columns and enable system versioning.

ALTER TABLE dbo.Employees
ADD
    ValidFrom DATETIME2
        GENERATED ALWAYS AS ROW START
        HIDDEN NOT NULL
        DEFAULT SYSUTCDATETIME(),

    ValidTo DATETIME2
        GENERATED ALWAYS AS ROW END
        HIDDEN NOT NULL
        DEFAULT CONVERT(
            DATETIME2,
            '9999-12-31 23:59:59.9999999'
        ),

    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

Then enable versioning with a history table:

ALTER TABLE dbo.Employees
SET
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.EmployeesHistory
    )
);

This example demonstrates the general approach for an existing table. Before applying it to a production database, verify the SQL Server version, table definition, existing constraints, and migration requirements.

If you already have a history table, it must meet SQL Server's requirements for temporal history tables. Existing historical data must also have trustworthy period values. SQL Server cannot reconstruct changes that occurred before temporal tracking was enabled.

The HIDDEN option is available in supported SQL Server versions and prevents the period columns from appearing in queries that use SELECT *. It does not prevent queries from explicitly selecting those columns.

Temporal Tables vs. Custom Audit Tables

Temporal tables are not a replacement for every auditing requirement. The right approach depends on the information your application needs to preserve.

Requirement	Temporal table	Custom audit table
Keep previous row versions	Built in	Must be implemented
Preserve deleted row versions	Built in	Must be implemented
Query data at a historical time	Built-in temporal queries	Requires custom query logic
Record which user made a change	Not provided automatically	Can be designed for this
Record the reason for a change	Not provided automatically	Can be designed for this
Store only selected fields or events	Stores row versions	Can be customized
Automatically capture changes made through SQL updates	Yes, while versioning is enabled	Depends on the implementation

A temporal table is a strong choice when the main requirement is to retain previous row versions. A custom audit table may be more appropriate when the application must record the user, request ID, reason, approval, or business event associated with a change.

Some systems need both approaches: temporal tables for historical data reconstruction and a separate audit mechanism for business accountability.

Performance and Production Considerations

Temporal tables reduce the amount of custom code needed for historical tracking, but they introduce storage and maintenance requirements.

History tables grow over time

Every qualifying update and delete can add a historical row. Frequently updated tables can generate large history tables, even when the current table remains relatively small.

Plan for history storage, backups, monitoring, and an explicit retention policy. SQL Server supports temporal history retention features in supported versions and configurations, but retention settings must match your operational and compliance requirements.

Add indexes for historical queries

Queries filtering by employee ID, order ID, or another business key may need suitable indexes on the history table.

SQL Server's automatically created history table may not have the indexes your reporting queries require. Review actual execution plans and query patterns before adding indexes. Indexes can improve reads but increase storage and the cost of maintaining the history data.

Understand temporal period timestamps

Temporal period values use the UTC transaction begin time. They are not guaranteed to represent the exact moment a specific statement changed a row.

Multiple changes within one transaction can therefore produce versions with the same system-time boundary values. A temporal table is not a reliable way to establish the exact sequence of every statement within a transaction.

If you need exact event ordering, request-level attribution, or a record of who performed a change, capture that information separately.

Changes to the table schema require care

When adding, removing, or changing columns, make sure the current and history table definitions remain compatible with temporal versioning requirements.

Some schema changes require system versioning to be disabled temporarily. Doing so creates a period during which SQL Server will not automatically record history unless the migration is carefully managed.

A production migration should include a backup, a tested rollback plan, and checks to ensure versioning is enabled again after the change.

Temporal history is not immutable audit evidence

Temporal tables preserve versions through SQL Server's normal temporal mechanism. They do not make history tamper-proof against privileged database operators or administrators.

If regulatory requirements demand tamper-resistant evidence, consider additional controls such as restricted permissions, independently protected audit storage, and appropriate database auditing.

Common Mistakes to Avoid

Assuming history exists before tracking begins: SQL Server does not automatically recover earlier versions of existing records.
Treating temporal timestamps as application event timestamps: Period values use the transaction begin time, not necessarily the exact statement execution time.
Expecting user information in the history table: User identity and change reasons are not automatically recorded as temporal metadata.
Ignoring history storage: A heavily updated table can accumulate a substantial amount of historical data.
Using SELECT * in historical reporting: Explicitly selecting columns makes queries more stable as table schemas change.
Disabling versioning without a migration plan: Changes made while system versioning is disabled will not be automatically captured by temporal history.
Assuming historical records can be restored automatically: Temporal queries retrieve older data, but recovery requires a deliberate insert or update operation.

Best Practices for SQL Server Temporal Tables

Enable temporal tracking on tables where historical reconstruction provides real business value.
Choose a retention period based on compliance, business requirements, and storage capacity.
Create appropriate history-table indexes after reviewing real query patterns.
Use FOR SYSTEM_TIME AS OF for point-in-time reporting and range queries for historical investigations.
Capture user identity, request identifiers, and change reasons separately when audit requirements demand them.
Test temporal behavior during inserts, updates, deletes, bulk operations, and schema migrations.
Monitor history-table growth and verify that backup and restore procedures cover the required historical data.
Avoid disabling system versioning during normal application operations unless there is a specific, controlled reason.

Summary

SQL Server temporal tables provide a practical way to track changes to database records without building custom history logic for every insert, update, and delete operation. SQL Server maintains current and historical row versions, and the FOR SYSTEM_TIME clause makes it possible to query the data as it existed at a particular time or across a range.

They are especially useful for historical reporting, investigating accidental updates, reviewing deleted records, and reconstructing earlier database states.

However, temporal tables do not automatically record who changed a row or why the change occurred. They also require storage planning, indexing, retention management, and careful schema migrations.

When your application needs reliable historical versions of database records, system-versioned temporal tables are a useful SQL Server feature. Combine them with a separate audit mechanism when your application needs a complete record of user actions and business events.