Joining Two History Tables at a Point in Time With FOR SYSTEM_TIME AS OF

Yesterday's customer record belongs with yesterday's price plan. Put FOR SYSTEM_TIME AS OF on both tables to reconstruct that relationship at one instant. Mixing a historical row with a current row answers a different question.

Give Each Table Its Own History

A temporal table keeps current rows and earlier row versions. SQL Server manages the period columns and writes replaced versions into its history table. Each table has its own timeline.

A foreign key between current tables doesn't force a historical query to use matching times. You must apply that time consistently when reading both sides of a relationship.

I check every joined table when reviewing a historical report. One missing temporal clause changes the meaning without making the query fail. Use a disposable database for this sample.

The period columns use datetime2, and the history names are explicit. Generated sample rows establish the relationship. They don't represent an observed customer history or a measured query result.

CREATE TABLE dbo.TemporalPlans
(
    PlanId int NOT NULL PRIMARY KEY,
    MonthlyPrice decimal(19,4) 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.TemporalPlansHistory));
CREATE TABLE dbo.TemporalCustomers
(
    CustomerId int NOT NULL PRIMARY KEY,
    PlanId int NOT NULL REFERENCES dbo.TemporalPlans(PlanId),
    CustomerName nvarchar(60) 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.TemporalCustomersHistory));
INSERT dbo.TemporalPlans(PlanId,MonthlyPrice) VALUES (1,25);
INSERT dbo.TemporalCustomers(CustomerId,PlanId,CustomerName) VALUES (1,1,N'Sample Customer');

Apply FOR SYSTEM_TIME AS OF to Both Sides

Capture a UTC moment after creating the sample rows. Use that same variable on both tables. FOR SYSTEM_TIME AS OF selects versions valid at that instant.

The period start is inclusive, and the period end is exclusive. That boundary prevents adjacent versions from both representing the same row at a transition. Alias placement follows the temporal clause.

The query joins customer and plan identifiers after selecting their historical versions. It doesn't join the history tables directly. SQL Server handles the current and history storage behind the temporal syntax.

For real data, choose the timestamp from the event you are investigating. A vague phrase such as yesterday needs an exact UTC instant before it becomes a reproducible query.

DECLARE @Moment datetime2 = SYSUTCDATETIME();
SELECT c.CustomerId, c.CustomerName, p.MonthlyPrice
FROM dbo.TemporalCustomers FOR SYSTEM_TIME AS OF @Moment AS c
JOIN dbo.TemporalPlans FOR SYSTEM_TIME AS OF @Moment AS p
  ON p.PlanId = c.PlanId;

Respect the Transaction Clock

Temporal period values use UTC transaction begin time. They don't record the moment each business action happened outside SQL Server. A long transaction can therefore give several changes an earlier shared boundary.

Distinguish system time from a business effective date. An insurance plan taking effect next month needs that business date stored separately from the engine's versioning period.

I ask which clock the report is meant to answer before tuning it. Convert a user's local timestamp to UTC deliberately, including daylight saving rules. Don't subtract a fixed offset for every historical date.

A clear timestamp with its time zone beats a screenshot of a clock. Time travel in a query still needs a valid destination.

Understand the Range Forms

BETWEEN returns versions overlapping an interval and includes versions starting at its upper boundary. CONTAINED IN returns versions whose complete validity period falls within the specified bounds. Those forms answer different questions from a snapshot.

A range can return several versions of one logical key. Expect that multiplicity before adding another temporal table to the join.

Joining two range results only by PlanId can pair versions that were never simultaneously valid. Add an interval-overlap predicate when you need matching history across a range. Alternatively, choose a series of snapshots with an explicit reporting rule.

The query below uses a two-day window around the sample rows. BETWEEN returns the current customer row. CONTAINED IN returns nothing, because that open version ends at the maximum datetime2 value.

Don't use DISTINCT to conceal an incorrect timeline join. Removing duplicate-looking output doesn't repair the relationship between the selected versions.

DECLARE @Start datetime2 = DATEADD(DAY, -1, SYSUTCDATETIME());
DECLARE @Finish datetime2 = DATEADD(DAY, 1, SYSUTCDATETIME());
SELECT CustomerId, PlanId, ValidFrom, ValidTo
FROM dbo.TemporalCustomers FOR SYSTEM_TIME BETWEEN @Start AND @Finish;
SELECT CustomerId, PlanId, ValidFrom, ValidTo
FROM dbo.TemporalCustomers FOR SYSTEM_TIME CONTAINED IN (@Start, @Finish);

Put the Period in the History Index

The history table needs indexes for the questions you ask. SQL Server creates a useful default clustered history index when it creates the history table. That index begins with the period end and start columns.

Inspect the actual definition before adding another. The query below adds a focused lookup index for customer history when key-based snapshots are a frequent request.

An equality predicate on CustomerId followed by period bounds can reduce unnecessary history reads. Included columns cover the returned relationship and name. Build a comparable plan index only when the workload supports it.

Every additional history index consumes storage and adds work during version insertion. A historical table deserves the same evidence-based index review as the current table.

CREATE INDEX IX_TemporalCustomersHistory_Lookup
ON dbo.TemporalCustomersHistory(CustomerId, ValidTo, ValidFrom)
INCLUDE (PlanId, CustomerName);

Check Missing Matches From FOR SYSTEM_TIME AS OF

A snapshot join can find no plan version for a customer. Start with a LEFT JOIN when investigating that gap. Inspect the selected period values and the earliest history retained.

Purged history limits how far back you can reconstruct relationships. The temporal clause doesn't recreate versions that were deleted through an approved retention process or never captured originally.

Which historical instant must your system still answer accurately? That requirement belongs in retention planning. Validate that both tables preserve enough history.

Also check whether versioning was disabled during a maintenance operation. A valid current foreign key is useful, but it doesn't certify that all past relationships remain available. Report the actual gap instead of silently substituting today's plan.

Save the FOR SYSTEM_TIME AS OF Moment With the Query

Keep the UTC timestamp, business meaning, and temporal clauses together in the report definition. State whether the result is a snapshot or an interval. Record how local input becomes UTC.

These choices are part of correctness, not formatting. Another reader should be able to rerun the same question without guessing what yesterday meant when you wrote it.

Use FOR SYSTEM_TIME AS OF consistently across every related temporal table. Check the actual plan for history reads and then add targeted indexes. Preserve the timestamp when comparing results after maintenance.

That gives you a historical report you can explain row by row. The point is matching versions that existed together, not collecting every old row the database remembers.

For a changing relationship, inspect the key value in the selected customer version too. Today's PlanId isn't necessarily yesterday's PlanId. The historical row supplies the correct join key. Carrying a current key into a historical lookup creates another quiet mismatch, even when both queries use the same timestamp.

Related reading on this blog: Temporal Tables: Keeping History Without a Trigger and Temporal Table Retention: Cleaning Out Old History Automatically.

A historical join is not a join to yesterday's customer alone, it is a shared point in time.

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