Who Changed This Row? Temporal History With FOR SYSTEM_TIME ALL

The current row hides the values it held before the last update. FOR SYSTEM_TIME ALL exposes temporal versions, but identifying who changed them still requires an actor recorded by the application or database code.

Separate Version Time From Actor Identity

A system-versioned temporal table preserves earlier row versions when rows change. Its period columns describe when versions were valid according to the database engine. The history does not automatically identify the person responsible for each update.

SQL Server 2016 introduced system-versioned temporal tables. A current table and its history table form the versioned record. FOR SYSTEM_TIME queries combine and filter those versions according to the requested temporal interval.

I check the period definition before interpreting the history. The engine uses UTC transaction-begin time for its version boundaries. Treating those values as local client time produces misleading timelines during a change review.

Record an actor separately when the investigation needs identity. SUSER_SNAME supplies the current login context, while SESSION_CONTEXT can carry an application-supplied identity. Neither automatically proves which human used a shared login.

A caller-controlled session value also needs validation and trusted assignment. A temporal version with an unverified actor label remains a version with an unverified actor label. History preserves what you stored, not the credibility of its source.

Create a Small Versioned Table

Run the demonstration in a disposable database. The current row includes a balance, an actor value, and period columns. SQL Server manages the generated period values while the application controls the ordinary business columns.

CREATE TABLE dbo.TemporalAccountDemo
(
    AccountID int NOT NULL PRIMARY KEY,
    Balance decimal(12,2) NOT NULL,
    ChangedBy sysname NOT NULL DEFAULT SUSER_SNAME(),
    ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL
        DEFAULT SYSUTCDATETIME(),
    ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL
        DEFAULT CONVERT(datetime2(7), '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON
      (HISTORY_TABLE = dbo.TemporalAccountDemoHistory));
INSERT dbo.TemporalAccountDemo(AccountID, Balance) VALUES (1, 100.00);

ChangedBy's default applies on insertion. An update must also set the actor column explicitly. Forgetting that update leaves the new version carrying an old actor value, which creates a convincing but false attribution.

The sample balance is input data. Your own execution and timestamps supply the evidence for the following queries.

Produce Changes in Separate Transactions

Use separate autocommit updates so the examples have distinct temporal boundaries. Several updates inside one transaction use the same transaction-begin timestamp. That can produce zero-duration historical versions.

WAITFOR DELAY '00:00:00.010';
UPDATE dbo.TemporalAccountDemo
SET Balance = 120.00, ChangedBy = SUSER_SNAME()
WHERE AccountID = 1;
WAITFOR DELAY '00:00:00.010';
UPDATE dbo.TemporalAccountDemo
SET Balance = 115.00, ChangedBy = SUSER_SNAME()
WHERE AccountID = 1;
SELECT AccountID, Balance, ChangedBy, ValidFrom, ValidTo
FROM dbo.TemporalAccountDemo FOR SYSTEM_TIME ALL
WHERE AccountID = 1
ORDER BY ValidFrom, ValidTo;

The short pauses make the demonstration easier to inspect. They do not measure application latency or represent a recommended production delay. Each update operates under the same login unless you deliberately test another permitted context.

FOR SYSTEM_TIME filters zero-duration versions out of its temporal result. If several updates occur in one transaction, query the history table directly when investigating those intermediate records. Do not assume ALL means every physical history row regardless of its duration.

Compare FOR SYSTEM_TIME ALL Versions With Previous Values

LAG reads the preceding version's value inside each account's ordered history. Compare that value with the current row in the temporal result. Filtering belongs outside the window calculation so the previous version remains available.

WITH Versions AS
(
    SELECT AccountID, Balance, ChangedBy, ValidFrom, ValidTo,
           LAG(Balance) OVER
           (PARTITION BY AccountID ORDER BY ValidFrom, ValidTo) AS PreviousBalance
    FROM dbo.TemporalAccountDemo FOR SYSTEM_TIME ALL
)
SELECT AccountID, PreviousBalance, Balance AS NewBalance,
       ChangedBy AS RecordedActor, ValidFrom AS ChangeBoundaryUtc
FROM Versions
WHERE PreviousBalance IS NOT NULL AND Balance <> PreviousBalance
ORDER BY AccountID, ValidFrom;

Balance is non-null in this demonstration, so a null preceding value identifies the first visible version. Nullable business columns need null-safe change detection. A comparison using only inequality misses changes between null and a non-null value.

The actor on the newer version identifies the actor value recorded for that version's write. The old version's actor describes an earlier write. Keep that distinction clear when displaying an old value beside its replacement.

A no-op update can still generate temporal history. Filtering unchanged balances removes those rows from this balance-change report. Other columns can still have changed, so choose the fields that define a meaningful change for your investigation.

Narrow FOR SYSTEM_TIME ALL to the Window You Mean

BETWEEN returns versions overlapping the requested period, including versions beginning exactly at its upper boundary. CONTAINED IN returns versions whose entire validity period sits inside the selected boundaries. Those are different questions.

DECLARE @To datetime2(7) = SYSUTCDATETIME();
DECLARE @From datetime2(7) = DATEADD(day, -1, @To);
SELECT AccountID, Balance, ChangedBy, ValidFrom, ValidTo
FROM dbo.TemporalAccountDemo FOR SYSTEM_TIME BETWEEN @From AND @To
WHERE AccountID = 1
ORDER BY ValidFrom;

SELECT AccountID, Balance, ChangedBy, ValidFrom, ValidTo
FROM dbo.TemporalAccountDemo FOR SYSTEM_TIME CONTAINED IN (@From, @To)
WHERE AccountID = 1
ORDER BY ValidFrom;

In the demo, BETWEEN returns all three versions and CONTAINED IN returns the two closed ones. The current version's end is far in the future, so it is not wholly contained in a window ending now. A BETWEEN query can include it when its validity overlaps the window. That difference explains apparently missing current values in a contained-history report.

If you want changes during a window, compare the complete needed version sequence before filtering the new version's start. Filtering earlier versions first can remove the predecessor required to calculate an accurate old value.

Keep the History Useful and Trustworthy

I keep the actor-assignment rule beside the table design. Every write path must follow it, including imports and administrative updates. One forgotten path is enough to make an attribution column inconsistent.

Which identity does your application actually authenticate? A shared SQL login describes a connection credential, not an individual user. Use a trusted application identity when the business needs that distinction, and document how it reaches the stored column.

Review history retention, permissions, and storage as part of operation. A temporal table does not supply an indefinite audit guarantee if history is removed or privileged users can alter its controls. State those limits when presenting a timeline as evidence.

Read FOR SYSTEM_TIME ALL Boundaries by Transaction Time

Long transactions can make the recorded validity boundary earlier than the moment an UPDATE statement ran. The period follows transaction-begin time. That behavior is correct for temporal versioning but surprises readers expecting statement-start time.

Keep related application audit evidence when precise action timing matters. Temporal history answers which versions existed under the engine's time model. It complements actor and request records rather than replacing all of them.

Preserve the original period values in UTC during investigation. Convert only for presentation using a defined time zone. Comparing converted local times across daylight-saving boundaries without their offsets creates another avoidable ambiguity.

Temporal tables have excellent memory and no detective's badge. A deletion ends the final version but does not create a new current row carrying the deleting actor. Record deletion identity separately when that question matters. Likewise, a shared login or an unchanged actor column cannot establish individual responsibility. Present the stored evidence and its limits together rather than letting the timeline imply more than the write path recorded. FOR SYSTEM_TIME ALL reads the temporal versions exposed by the system-time query. Compare FOR SYSTEM_TIME ALL with direct history inspection when transaction-time boundaries hide an intermediate version.

Related reading on this blog: Temporal Table Retention: Cleaning Out Old History Automatically and Who Changed This Row? Auditing Data Changes.

Temporal history is not automatic attribution, it is a version timeline that needs a trustworthy actor beside it.

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