Time as a First Class Dimension
A temporal table builds history into the table itself. Each row carries a validity period, and the database automatically maintains old versions when rows change. You can then query the table as of any past moment.
System Versioned Tables
- Each row gets a row start and row end timestamp marking when it was current.
- When a row is updated, the database closes the old version by setting its row end and inserts a new current version.
- A query with an as of clause returns the versions valid at that time.
Two Kinds of Time
- System time records when a fact was stored in the database.
- Valid time records when a fact was true in the real world.
- A bitemporal table tracks both, letting you ask what the database believed on one date about a fact valid on another.
Why It Matters
- No manual triggers; history is maintained by the engine.
- Point in time queries become a clean as of clause rather than ad hoc joins.
- It is the disciplined version of an audit table for state, not events.
Key idea
Temporal tables attach validity periods to rows so the engine maintains history automatically and queries can read the data as it was at any past time.