SQL Server Temporal History: What AS OF Actually Means

When a price changes from 10 to 12, a normal table keeps only the new value unless the application writes its own history. A system-versioned temporal table can preserve previous row versions automatically. That is useful for investigations and reconstruction, but it does not mean every historical question has the same answer.

Separate three questions: what value was recorded under SQL Server's system-time rules, when did the business intend that value to take effect, and who authorized the change? Temporal history directly addresses the first. The other two need additional data and a clear process.

Observe current and historical versions

This SQL Server 2016-or-later example creates permanent practice tables. Run it in autocommit mode without an enclosing transaction.

-- Use a disposable database, autocommit, and no enclosing transaction.
IF @@TRANCOUNT <> 0 THROW 50001, 'Use a separate practice connection.', 1;
CREATE TABLE dbo.PriceTemporalDemo (
    ProductId int NOT NULL PRIMARY KEY,
    Price decimal(12,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.PriceTemporalDemoHistory
));
INSERT dbo.PriceTemporalDemo(ProductId, Price) VALUES (1, 10.00);
DECLARE @BeforeChange datetime2(7) = SYSUTCDATETIME();
WAITFOR DELAY '00:00:01';
UPDATE dbo.PriceTemporalDemo SET Price = 12.00 WHERE ProductId = 1;
SELECT ProductId, Price FROM dbo.PriceTemporalDemo WHERE ProductId = 1;
SELECT ProductId, Price, ValidFrom, ValidTo
FROM dbo.PriceTemporalDemo FOR SYSTEM_TIME AS OF @BeforeChange
WHERE ProductId = 1;

The current query returns 12.00, while AS OF @BeforeChange returns 10.00. SQL Server searches the relevant current and history versions through the temporal syntax. Applications do not have to manually union the two tables for this point-in-time lookup.

The period uses UTC datetime2 values. AS OF selects a version whose start is at or before the requested instant and whose end is after it. The upper boundary is exclusive. Use an appropriately converted UTC parameter rather than passing a local wall-clock value with an assumed offset.

The pause exists only to separate the two demonstration instants. It is not part of a production design. Without a clear separation, a tiny sample can make it difficult to distinguish timestamps, particularly if somebody changes the type precision.

The all-versions query makes the intervals visible.

SELECT ProductId, Price, ValidFrom, ValidTo
FROM dbo.PriceTemporalDemo FOR SYSTEM_TIME ALL
WHERE ProductId = 1
ORDER BY ValidFrom, ValidTo;

An update can create history even when the assigned values are unchanged. Avoid unnecessary writes when they create expensive history churn, but do not remove changes that are meaningful to the business merely to reduce storage.

Understand the transaction clock

Period boundaries are based on the transaction's begin time, not its commit time. A long-running transaction can therefore introduce a version whose system start precedes the moment another connection could first observe its committed value. AS OF follows these system-time semantics; it is not a recording of exactly what every concurrent reader saw.

Several updates to the same row inside one transaction can produce zero-duration versions. Temporal query clauses filter out zero-duration history versions; inspecting the history table directly may reveal records absent from FOR SYSTEM_TIME output. Do not interpret every missing intermediate state as data loss.

If a price is entered today but should apply next month, store a separate business-effective date or interval. Do not attempt to force the generated system period to represent that business schedule. Likewise, correcting a backdated business fact should preserve the distinction between the fact's effective date and when the database learned it.

Applying AS OF to several temporal tables at the same instant can simplify historical joins. Still inspect which tables are temporal and what non-temporal lookup values contribute. Joining an old order version to today's mutable category name can produce a mixed-time report that looks historical but is not.

Operate the history as real data

Estimate history growth from update frequency and row width, not only the number of current rows. A small frequently modified table can accumulate substantial history. Index the actual investigation pattern; filtering one product through many versions differs from reconstructing the entire database at one instant.

Retention and cleanup must be intentional. A historical report cannot reconstruct versions already removed under the retention policy. Document that available horizon and monitor the cleanup mechanism appropriate to the deployed version.

Temporal history is not a substitute for backups, nor an immutable audit trail. It does not automatically record the business actor or reason, and privileged maintenance can change the history configuration. Store required attribution separately and protect its access according to the application's needs.

Plan schema changes and maintenance across both current and history tables. Turning SYSTEM_VERSIONING off creates a period during which automatic history capture is not active. When re-enabling, explicitly reconnect the intended history table instead of accidentally starting a new one.

Test an update, a delete, a long transaction, multiple changes in one transaction, and boundaries exactly at a version transition. A useful temporal design lets the reader distinguish what the system recorded from what the business meant.

Technical references: Microsoft Learn: Query temporal data · Microsoft Learn: Temporal considerations.