When a business record changes, developers often need to know what its previous values were. An order may move from Pending to Approved, an employee's department may change, or a product price may be updated. If the application stores only the latest values, investigating the change later can be difficult.
One common solution is to create audit tables and database triggers that copy old values whenever a record changes. This approach works, but it requires additional SQL code and careful maintenance. Every relevant table needs a reliable auditing strategy, and schema changes must be reflected in the audit logic.
SQL Server system-versioned temporal tables provide another option. They automatically preserve previous versions of rows when data changes, allowing developers to query historical records using ordinary SQL.
This article explains how temporal tables work, how to create and query them, and when they are useful in production applications.
What Is a Temporal Table?
A temporal table maintains a history of row versions over time. SQL Server stores the current version in the main table and previous versions in an associated history table.
A system-versioned temporal table uses two period columns:
ValidFrom identifies the start of the row version's system-time period.ValidTo identifies the end of that period.
These columns are managed by SQL Server when system versioning is enabled. When a row is updated or deleted, the previous version is moved into the history table, and the period values identify when that version was current according to the database's system-time rules.
For example, suppose a product's price changes from 100 to 120. The current table contains the new price, while the history table retains the earlier row version.
Temporal tables are useful for investigating changes, comparing previous values, and reconstructing the database state at an earlier system time.
They do not automatically record every business detail about a change. For example, they do not inherently identify which application user initiated an update. If you need that information, you must capture it separately.
Create a System-Versioned Temporal Table
Consider an application that manages product prices. The table needs the product ID, product name, price, and two period columns.
The following SQL creates a temporal table and its history table:
CREATE TABLE dbo.Products
(
ProductId INT NOT NULL
CONSTRAINT PK_Products PRIMARY KEY,
ProductName NVARCHAR(200) NOT NULL,
Price DECIMAL(18, 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.ProductsHistory
)
);
The GENERATED ALWAYS definitions tell SQL Server to manage the period values. PERIOD FOR SYSTEM_TIME identifies the columns used to track each row version.
The SYSTEM_VERSIONING = ON setting enables automatic history management. SQL Server creates the specified history table when it does not already exist and the configuration is valid.
The history table stores previous row versions. You should not normally modify it directly because SQL Server manages it as part of the temporal-table mechanism.
Insert and Update Data
After creating the table, insert a product as usual:
INSERT INTO dbo.Products
(
ProductId,
ProductName,
Price
)
VALUES
(
1,
N'Wireless Keyboard',
100.00
);
Now update the price:
UPDATE dbo.Products
SET Price = 120.00
WHERE ProductId = 1;
SQL Server preserves the previous row version in the history table when the update occurs. The current table contains the updated price, while the historical version contains the earlier price.
You do not need to write a separate trigger to copy the old values.
You can inspect the current row and its period columns:
SELECT
ProductId,
ProductName,
Price,
ValidFrom,
ValidTo
FROM dbo.Products
WHERE ProductId = 1;
To inspect the history table directly, you can query it:
SELECT
ProductId,
ProductName,
Price,
ValidFrom,
ValidTo
FROM dbo.ProductsHistory
WHERE ProductId = 1
ORDER BY ValidFrom;
The history table is useful when investigating a record's previous versions. However, temporal queries are often more convenient because they can combine current and historical data automatically.
Query Historical Data with FOR SYSTEM_TIME
SQL Server provides the FOR SYSTEM_TIME clause to query a temporal table across its current and historical versions.
View All Versions of a Record
Use FOR SYSTEM_TIME ALL to retrieve the current and historical row versions:
SELECT
ProductId,
ProductName,
Price,
ValidFrom,
ValidTo
FROM dbo.Products
FOR SYSTEM_TIME ALL
WHERE ProductId = 1
ORDER BY ValidFrom;
This query can show the product's previous and current prices in one result set.
It is useful for reviewing how a record changed without manually joining the current table to its history table.
View Data at a Specific Time
Use AS OF to query the table as it existed at a particular system time:
DECLARE @CheckTime DATETIME2 =
'2026-10-15T10:00:00';
SELECT
ProductId,
ProductName,
Price
FROM dbo.Products
FOR SYSTEM_TIME AS OF @CheckTime
WHERE ProductId = 1;
Replace the example timestamp with the point in time you want to investigate.
SQL Server evaluates the temporal versions against the specified system time. The result represents the row versions considered valid at that time according to the temporal period columns.
This is useful when investigating an unexpected price, reviewing a configuration change, or determining what information was stored when a problem occurred.
Remember that temporal tables track system time, not necessarily the business time when an event was intended to take effect. They also do not automatically preserve every intermediate operation within a transaction as a separate version.
View Changes During a Time Range
The FROM ... TO syntax returns versions that were active at any point in the specified interval:
SELECT
ProductId,
ProductName,
Price,
ValidFrom,
ValidTo
FROM dbo.Products
FOR SYSTEM_TIME FROM
'2026-10-01T00:00:00'
TO
'2026-10-16T00:00:00'
WHERE ProductId = 1
ORDER BY ValidFrom;
This is helpful when reviewing the versions that existed during a reporting period.
The range boundaries follow SQL Server's temporal query semantics. Choose the range carefully when building reports, especially when adjacent reporting windows must not produce confusing results.
How Temporal Tables Handle Deletes
Temporal tables preserve historical data when a row is deleted while system versioning is enabled.
For example:
DELETE FROM dbo.Products
WHERE ProductId = 1;
The current row is removed from dbo.Products, but SQL Server retains its previous version in the history table.
You can still query the row's historical state:
SELECT
ProductId,
ProductName,
Price,
ValidFrom,
ValidTo
FROM dbo.Products
FOR SYSTEM_TIME ALL
WHERE ProductId = 1;
This behavior can help investigate accidental deletions or understand how a record looked before it was removed.
However, history retention needs to be managed. Historical records can continue to consume storage as the application updates and deletes data. A production system should define how long history must be retained and how older versions will be archived or removed according to business and compliance requirements.
Temporal Tables vs. Custom Audit Triggers
Both temporal tables and audit triggers can preserve information about changes, but they have different responsibilities.
| Feature | Temporal tables | Custom audit triggers |
|---|
| Historical row versions | Managed automatically by SQL Server | Must be implemented |
| Previous values after updates | Preserved automatically | Trigger must capture them |
| Previous values after deletes | Preserved automatically | Trigger must handle deletes |
| Querying past database state | Supported through FOR SYSTEM_TIME | Requires custom query logic |
| Recording the application user | Requires additional design | Can be implemented in trigger logic |
| Custom business-event details | Requires additional design | Can be implemented in trigger logic |
| Maintenance | Less custom history logic | Trigger and audit schema require maintenance |
Temporal tables are a strong choice when the main requirement is to preserve row history and query previous database states.
Triggers may be more appropriate when the audit record needs additional information, such as a business reason, request identifier, application user, or a custom event description. They can also be used alongside temporal tables when both requirements exist.
Do not assume that temporal tables replace every audit or compliance system. They solve a specific problem: maintaining system-time versions of rows.
Important Considerations for Production
Plan for History Table Growth
Every update or delete can add historical data. Tables with frequent changes may produce large history tables over time.
Monitor history-table size and query performance. Define retention policies based on actual business requirements, and use SQL Server's supported retention features where available for your version and edition.
Index Historical Data for Real Queries
History queries can become expensive when the history table contains millions of versions.
Review the access patterns used by reports and investigations. Add suitable indexes when appropriate, and check execution plans for queries that filter by business keys or time periods.
Do not add indexes without considering the additional storage and maintenance costs.
Understand Transaction Time Semantics
Temporal period values are based on the transaction's system-time behavior, not necessarily the exact wall-clock moment when each SQL statement executed.
A long-running transaction can therefore produce period values that differ from the time an application user thinks the change occurred.
If you need to record the business event time separately, store it in an appropriate column.
Control Access to Historical Information
Historical rows may contain personal or confidential information that has since been changed or deleted from the current table.
Restrict access to history data according to your security requirements. A record disappearing from the current table does not mean its previous values have disappeared from the database.
Test Schema Changes Carefully
Temporal tables have additional rules for schema changes and history management. Test migrations against a representative environment before changing a temporal table or its history table.
Keep current and historical schemas consistent according to SQL Server's temporal-table requirements.
When Should You Use Temporal Tables?
Consider system-versioned temporal tables when your application needs to:
- Investigate previous values after a record changes.
- Reconstruct data as it existed at a particular system time.
- Preserve previous row versions without maintaining custom update and delete triggers.
- Review changes to product prices, customer records, or configuration data.
- Support reporting that compares current and historical database states.
They may not be sufficient on their own when the requirement is to record who made a change, why it happened, or which business operation initiated it. Those details require an additional auditing design.
Conclusion
SQL Server temporal tables provide a built-in way to preserve previous row versions without writing custom triggers for every update and delete operation. With SYSTEM_VERSIONING = ON, SQL Server manages the history table and period columns, while FOR SYSTEM_TIME makes historical queries straightforward.
They are particularly useful when developers need to investigate previous values or reconstruct the database state at an earlier system time.
Before introducing temporal tables into production, plan for history growth, indexing, access control, retention, and schema changes. When those concerns are addressed, temporal tables can simplify historical data tracking and reduce the amount of custom auditing code your application needs.
Join the conversation! Your thoughts help the community grow.