SQL 111 🛢️ Temporal Tables and System-Versioned Data History - KandZ

⏱️ Estimated read: 16 min | |

Temporal tables solve a problem that ordinary tables cannot: preserving every past state of a row while keeping the current state fast to query. The SQL:2011 standard introduced system-versioned temporal tables, and the major relational databases have implemented the concept with different syntax and storage models. SQL Server calls them system-versioned temporal tables and stores history in a separate table. Db2 uses the same two-table model with SYSTEM_TIME and BUSINESS_TIME periods. MariaDB supports system versioning with optional partitioning to separate current and historical data. Oracle approaches the problem through Flashback Data Archive (now Flashback Time Travel), which archives changes to a table and allows AS OF queries without a separate history table exposed to the user. PostgreSQL 18 introduced temporal constraints, but they are a different concept from system-versioned history and should not be confused with the SQL Server model .

The common pattern across implementations is the period: two timestamp columns that define when a row version was valid. SQL Server requires ValidFrom and ValidTo with GENERATED ALWAYS AS ROW START/END and a PERIOD FOR SYSTEM_TIME clause . Db2 defines a system period with begin and end columns where the begin is inclusive and the end is exclusive . MariaDB allows a short form with WITH SYSTEM VERSIONING that generates invisible period columns automatically . The query interface is similarly consistent: FOR SYSTEM_TIME AS OF, BETWEEN, FROM, and CONTAINED IN allow querying the table as it existed at a point in time or over an interval .

This chapter covers the system-versioned model as implemented in SQL Server and Db2, the MariaDB partitioning variant, the Oracle Flashback alternative, and the practical decisions around retention, indexing, and query patterns. It assumes familiarity with basic DDL and query syntax.

Key point: A system-versioned temporal table is two tables from the user’s perspective: the current table holds the active rows, and the history table holds every previous version. The database engine moves rows automatically on update and delete. Queries without FOR SYSTEM_TIME hit only the current table; queries with it union the current and history tables. The retention policy is the only thing that deletes history—the engine never purges it.

Why temporal tables exist as a database feature

Auditing requires history that application code cannot reliably maintain. A trigger-based audit table can record changes, but it must be written correctly for every table, and it must handle updates, deletes, and schema changes. A system-versioned temporal table moves that responsibility into the engine. When a row is updated, the previous version is copied to the history table with its validity period closed. When a row is deleted, it is moved to the history table rather than removed. The application does not need audit triggers or change-data-capture infrastructure .

Slowly changing dimensions in data warehouses need row versioning. The Microsoft documentation on temporal table usage scenarios describes Type 2 and Type 4 slowly changing dimensions, where each version of a dimension member is a separate row with a validity period. A system-versioned temporal table is closest to Type 4: the current table holds the active version, and the history table holds the previous versions. The ETL layer no longer needs to maintain the versioning logic; the engine does it automatically .

Point-in-time queries need a consistent view of past state. Oracle Flashback Query allows SELECT ... AS OF TIMESTAMP to retrieve data as it existed at a past time, using undo data for recent changes and Flashback Data Archive for longer retention . SQL Server and Db2 use the history table for the same purpose. The difference is that Flashback uses undo/archive storage that is not exposed as a separate table, while system-versioned tables expose the history table for direct querying.

The trade-off. Temporal tables increase storage consumption because every update and delete creates a history row. The history table grows without bound unless a retention policy deletes old rows. Queries without FOR SYSTEM_TIME are unaffected because they hit only the current table, but schema changes to the current table must be reflected in the history table. The benefit is automatic, engine-maintained history with no application code.

a. SQL Server: system-versioned tables with explicit history

SQL Server 2016 introduced system-versioned temporal tables. The table definition requires a primary key, two DATETIME2 columns marked GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END, and a PERIOD FOR SYSTEM_TIME declaration. The SYSTEM_VERSIONING clause enables the feature and optionally names the history table .

-- Create a system-versioned temporal table with an explicit history table.
CREATE TABLE Employee (
    EmployeeID   INT NOT NULL PRIMARY KEY CLUSTERED,
    Name         NVARCHAR(60) NOT NULL,
    Salary       DECIMAL(6,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.EmployeeHistory));

When a row is updated, the engine writes the previous version into EmployeeHistory with ValidTo set to the update time and ValidFrom set to the original insert time. The current row’s ValidFrom is set to the update time and ValidTo is set to 9999-12-31. When a row is deleted, it moves to the history table with the validity period closed .

Querying past state uses FOR SYSTEM_TIME:

-- Current rows only (no FOR SYSTEM_TIME clause).
SELECT * FROM Employee WHERE EmployeeID = 1000;

-- All versions, including current, ordered by validity.
SELECT * FROM Employee
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

-- State as of a specific timestamp.
SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2025-01-15 09:00:00'
WHERE EmployeeID = 1000;

-- Versions valid during an interval.
SELECT * FROM Employee
FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-06-30'
WHERE EmployeeID = 1000;

The ALL option returns every version, AS OF returns the version valid at the timestamp, BETWEEN returns versions that overlap the interval, and CONTAINED IN returns versions fully contained in the interval . The CONTAINED IN and ALL options are SQL Server extensions beyond the SQL:2011 standard .

b. Db2: system period and business period

Db2 supports two period types: SYSTEM_TIME for system-maintained history, and BUSINESS_TIME for application-defined validity. A temporal table can have both, making it bitemporal. The system period columns are maintained by the engine; the business period columns are supplied by the application .

-- Create a system-period temporal table.
CREATE TABLE Policy (
    PolicyID     INT NOT NULL PRIMARY KEY,
    Premium      DECIMAL(10,2) NOT NULL,
    SysStart     TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
    SysEnd       TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD SYSTEM_TIME (SysStart, SysEnd)
);

-- Create the history table and enable versioning.
CREATE TABLE PolicyHistory (
    PolicyID     INT NOT NULL,
    Premium      DECIMAL(10,2) NOT NULL,
    SysStart     TIMESTAMP(12) NOT NULL,
    SysEnd       TIMESTAMP(12) NOT NULL
);

ALTER TABLE Policy
ADD VERSIONING USE HISTORY TABLE PolicyHistory;

The Db2 documentation emphasizes that the begin column value is inclusive and the end column value is exclusive. A row with SysStart = '2025-01-01' and SysEnd = '2025-03-21' is valid on January 1 but not on March 21 . This differs from SQL Server’s closed-open interval semantics only in the default sentinel values; the overlap logic is the same.

Db2’s query syntax mirrors SQL Server’s:

-- State as of a timestamp.
SELECT * FROM Policy
FOR SYSTEM_TIME AS OF '2025-01-15 09:00:00'
WHERE PolicyID = 42;

Db2 also supports temporal logical transactions, which allow a batch job to record a more granular transaction timestamp than the default statement start time. The SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME global variable controls this scope .

c. MariaDB: system versioning with partitioning

MariaDB’s system-versioned tables use a shorter syntax. The WITH SYSTEM VERSIONING clause creates invisible period columns (ROW_START and ROW_END) and enables versioning. The history is stored in the same table by default, but partitioning by SYSTEM_TIME separates current and historical rows into different partitions .

-- Short form: invisible period columns.
CREATE TABLE user (
    id INT PRIMARY KEY,
    level VARCHAR(20)
) WITH SYSTEM VERSIONING;

-- Partitioned form: history in separate partitions.
CREATE TABLE user (
    id INT PRIMARY KEY,
    level VARCHAR(20)
) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur CURRENT
);

The partitioned form is the practical advantage. Current-data queries hit only the p_cur partition, avoiding the overhead of scanning historical rows that are mixed into the same table. History partitions can be rotated by time (INTERVAL 1 WEEK) or by size (LIMIT 100000), which provides a built-in retention mechanism .

MariaDB also supports transaction-precise history, where the FOR SYSTEM_TIME clause can reference a transaction ID rather than a timestamp:

SELECT * FROM user FOR SYSTEM_TIME AS OF TRANSACTION 12345;

This requires the mysql.transaction_registry table and is not compatible with SYSTEM_TIME partitioning .

d. Oracle: Flashback Data Archive

Oracle does not expose a separate history table for system versioning. Instead, Flashback Data Archive (renamed Flashback Time Travel in recent releases) automatically tracks transactional changes to a table and allows querying past states without a separate table in the user’s schema .

-- Enable Flashback Time Travel on a new table.
CREATE TABLE employee (
    empno NUMBER(4) NOT NULL,
    ename VARCHAR2(10),
    job   VARCHAR2(9)
) FLASHBACK ARCHIVE;

-- Enable on an existing table.
ALTER TABLE departments FLASHBACK ARCHIVE;

Querying uses AS OF:

SELECT * FROM employee
AS OF TIMESTAMP TO_TIMESTAMP('2025-01-15 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE ename = 'SMITH';

The archive has a retention period, defaulting to 60 days on Autonomous Database. The DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION procedure changes it . The storage is managed by Oracle, not exposed as a queryable history table. The query semantics are similar to SQL Server’s AS OF, but the implementation and the storage model differ.

Oracle’s Flashback Query also has a limitation: DDL operations on the table invalidate undo data for times before the DDL. Querying past a DDL operation returns an error rather than the state before the schema change . System-versioned tables in SQL Server and Db2 do not have this limitation for the history data itself, though schema changes still require the history table to match the current table’s structure.

Complete Example Session

-- ============================================
-- PART 1: SQL SERVER — CREATE TEMPORAL TABLE
-- ============================================
CREATE TABLE Product (
    ProductID   INT NOT NULL PRIMARY KEY CLUSTERED,
    Name        NVARCHAR(100) NOT NULL,
    Price       DECIMAL(10,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.ProductHistory));

-- ============================================
-- PART 2: INSERT AND UPDATE
-- ============================================
INSERT INTO Product (ProductID, Name, Price)
VALUES (1, 'Widget', 29.99);

-- Later: price change.
UPDATE Product SET Price = 24.99 WHERE ProductID = 1;

-- Current table: one row, Price 24.99.
-- History table: one row, Price 29.99, closed period.

-- ============================================
-- PART 3: QUERY CURRENT VS HISTORY
-- ============================================
-- Current only.
SELECT * FROM Product WHERE ProductID = 1;

-- All versions.
SELECT * FROM Product
FOR SYSTEM_TIME ALL
WHERE ProductID = 1
ORDER BY ValidFrom;

-- State before the update.
SELECT * FROM Product
FOR SYSTEM_TIME AS OF '2025-01-15 00:00:00'
WHERE ProductID = 1;

-- ============================================
-- PART 4: DB2 — SYSTEM PERIOD
-- ============================================
CREATE TABLE Policy (
    PolicyID   INT NOT NULL PRIMARY KEY,
    Premium    DECIMAL(10,2) NOT NULL,
    SysStart   TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
    SysEnd     TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD SYSTEM_TIME (SysStart, SysEnd)
);

CREATE TABLE PolicyHistory (
    PolicyID   INT NOT NULL,
    Premium    DECIMAL(10,2) NOT NULL,
    SysStart   TIMESTAMP(12) NOT NULL,
    SysEnd     TIMESTAMP(12) NOT NULL
);

ALTER TABLE Policy
ADD VERSIONING USE HISTORY TABLE PolicyHistory;

-- ============================================
-- PART 5: MARIADB — PARTITIONED SYSTEM VERSIONING
-- ============================================
CREATE TABLE user (
    id INT PRIMARY KEY,
    level VARCHAR(20)
) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur CURRENT
);

-- Current queries hit p_cur only.
SELECT * FROM user WHERE id = 42;

-- History queries use FOR SYSTEM_TIME.
SELECT * FROM user
FOR SYSTEM_TIME AS OF NOW() - INTERVAL 30 DAY
WHERE id = 42;

-- ============================================
-- PART 6: ORACLE — FLASHBACK ARCHIVE
-- ============================================
CREATE TABLE employee (
    empno NUMBER(4) NOT NULL,
    ename VARCHAR2(10),
    salary NUMBER(8,2)
) FLASHBACK ARCHIVE;

SELECT * FROM employee
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' DAY)
WHERE ename = 'SMITH';

-- ============================================
-- PART 7: RETENTION AND STORAGE
-- ============================================
-- SQL Server: no automatic purge.
-- DELETE FROM ProductHistory
-- WHERE ValidTo < DATEADD(year, -5, GETUTCDATE());

-- Db2: history table can be pruned manually.
-- DELETE FROM PolicyHistory
-- WHERE SysEnd < CURRENT TIMESTAMP - 5 YEARS;

-- MariaDB: partition rotation by time or size.
-- PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH PARTITIONS 12;

-- Oracle: retention set on the archive.
-- DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION(365);

-- ============================================
-- PART 8: INDEXING THE HISTORY TABLE
-- ============================================
-- SQL Server: clustered columnstore for analytics.
CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductHistory
ON ProductHistory;

-- Db2: index on period columns for AS OF queries.
CREATE INDEX IX_PolicyHistory_Period
ON PolicyHistory (SysStart, SysEnd);

-- ============================================
-- PART 9: SLOWLY CHANGING DIMENSION USE
-- ============================================
-- Temporal tables are closest to Type 4 SCD:
-- current table = active dimension rows.
-- history table = previous versions.
-- No ETL code to maintain versioning.
-- Query with FOR SYSTEM_TIME for reports.

-- ============================================
-- PART 10: THE COMPLETE FLOW
-- ============================================
-- 1. Define period columns and enable versioning.
-- 2. Engine moves old rows to history on update/delete.
-- 3. Query current table normally.
-- 4. Query history with FOR SYSTEM_TIME.
-- 5. Prune history with a retention policy.
-- 6. Index history for temporal queries.

Quick Reference

Implementation Comparison

Database	Syntax	Storage	Query Clause
SQL Server	`SYSTEM_VERSIONING = ON`	Separate history table	`FOR SYSTEM_TIME`
Db2	`ADD VERSIONING USE HISTORY TABLE`	Separate history table	`FOR SYSTEM_TIME`
MariaDB	`WITH SYSTEM VERSIONING`	Same table (or partitioned)	`FOR SYSTEM_TIME`
Oracle	`FLASHBACK ARCHIVE`	Archive (not exposed)	`AS OF`

`FOR SYSTEM_TIME` Options

Option	Returns
`AS OF <ts>`	State at the timestamp
`BETWEEN <t1> AND <t2>`	Versions overlapping interval
`FROM <t1> TO <t2>`	Versions starting in interval
`CONTAINED IN <t1, t2>`	Versions fully inside interval
`ALL`	Every version, including current

Period Column Rules

Database	Begin	End
SQL Server	`GENERATED ALWAYS AS ROW START`	`GENERATED ALWAYS AS ROW END`
Db2	`GENERATED ALWAYS AS ROW BEGIN`	`GENERATED ALWAYS AS ROW END`
MariaDB	Invisible `ROW_START`	Invisible `ROW_END`

Retention

Database	Mechanism
SQL Server	Manual `DELETE` from history
Db2	Manual `DELETE` from history
MariaDB	Partition rotation by time/size
Oracle	Archive retention setting

Best Practices

✅ Do This:

-- Define period columns as NOT NULL.                         // ✅
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL

-- Index the history table period columns.                    // ✅
CREATE INDEX IX_History_Period ON HistoryTable (SysStart, SysEnd);

-- Use a columnstore index for analytical history queries.    // ✅
CREATE CLUSTERED COLUMNSTORE INDEX ON HistoryTable;

-- Set a retention policy and prune history.                  // ✅
DELETE FROM HistoryTable WHERE ValidTo < DATEADD(year, -5, GETDATE());

-- Query current data without FOR SYSTEM_TIME.                // ✅
SELECT * FROM Product WHERE ProductID = 1;

❌ Don’t Do This:

-- Assume history is purged automatically.                    // ❌
-- SQL Server and Db2 never purge history.

-- Query FOR SYSTEM_TIME ALL on every request.                // ❌
-- It unions current and history; expensive.

-- Forget to update history table schema on DDL.              // ❌
-- Schema changes to current table require history changes.

-- Use temporal tables for volatile, high-update data.        // ❌
-- History grows fast; prune aggressively.

Common Pitfalls

Pitfall	Why It Happens	Fix
History table grows unbounded	No retention policy	Manual `DELETE` or partition rotation
`AS OF` returns unexpected rows	Timestamp granularity/timezone	Use UTC and precise timestamps
Schema change fails	History table structure mismatch	Alter both tables
Query performance degrades	Missing period index	Index `SysStart`/`SysEnd`
Oracle DDL invalidates history	Undo/archive limitation	Avoid querying across DDL

Real-World Examples

1. SQL Server AS OF Query

SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2025-01-15 09:00:00'
WHERE EmployeeID = 1000;

2. Db2 System Period

PERIOD SYSTEM_TIME (SysStart, SysEnd)

3. MariaDB Short Form

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING;

4. Oracle Flashback Query

SELECT * FROM employee AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

5. History Retention

DELETE FROM ProductHistory WHERE ValidTo < DATEADD(year, -5, GETUTCDATE());

6. History Index

CREATE INDEX IX_History ON ProductHistory (ValidFrom, ValidTo);

7. Columnstore History

CREATE CLUSTERED COLUMNSTORE INDEX IX ON ProductHistory;

8. SCD Type 4 Mapping

-- Current table = active dimension.
-- History table = previous versions.

9. `ALL` Query

SELECT * FROM Product FOR SYSTEM_TIME ALL WHERE ProductID = 1;

10. Partitioned History

PARTITION BY SYSTEM_TIME (PARTITION p_hist HISTORY, PARTITION p_cur CURRENT);

Visual: System-Versioned Table Model

┌──────────────────────────────────────────────┐
│  CURRENT TABLE          HISTORY TABLE        │
│  (active rows)          (previous versions)  │
│                                              │
│  ProductID: 1           ProductID: 1         │
│  Price: 24.99           Price: 29.99         │
│  ValidFrom: 2025-02-01  ValidFrom: 2025-01-01│
│  ValidTo: 9999-12-31    ValidTo: 2025-02-01  │
│                                              │
│  UPDATE moves old row to history.            │
│  DELETE moves row to history.                │
│  INSERT adds only to current.                │
│                                              │
│  Query without FOR SYSTEM_TIME → current only│
│  Query with FOR SYSTEM_TIME → union          │
│                                              │
└──────────────────────────────────────────────┘

Summary

Item	Value
SQL Server syntax	`SYSTEM_VERSIONING = ON` with `PERIOD FOR SYSTEM_TIME`
Db2 syntax	`ADD VERSIONING USE HISTORY TABLE`
MariaDB syntax	`WITH SYSTEM VERSIONING` (partition optional)
Oracle approach	Flashback Archive, `AS OF` query
Query clause	`FOR SYSTEM_TIME AS OF / BETWEEN / ALL`
Storage	Current table + history table (except Oracle)
Retention	Manual purge (SQL Server, Db2); partition rotation (MariaDB)
SCD mapping	Closest to Type 4

Key takeaways:

System-versioned temporal tables keep current and historical rows in separate tables. The engine moves rows to history on update and delete. Queries without FOR SYSTEM_TIME hit only the current table; queries with it union both .
The period columns define validity. SQL Server uses ValidFrom/ValidTo with GENERATED ALWAYS; Db2 uses SysStart/SysEnd with SYSTEM_TIME; MariaDB generates invisible columns with WITH SYSTEM VERSIONING .
FOR SYSTEM_TIME is the query interface. AS OF returns the state at a timestamp, BETWEEN returns versions overlapping an interval, and ALL returns every version including current .
History is never purged automatically. SQL Server and Db2 require manual DELETE or a scheduled job. MariaDB supports partition rotation by time or size, which provides built-in retention .
Oracle uses a different model. Flashback Data Archive tracks changes without exposing a separate history table. Query with AS OF TIMESTAMP; the archive has a retention setting .
Temporal tables are closest to Type 4 slowly changing dimensions. The current table holds the active version, and the history table holds previous versions. The ETL layer no longer needs to maintain versioning logic .
PostgreSQL 18 temporal constraints are not the same feature. They enforce temporal validity rules but do not provide system-versioned history tables. Do not confuse the two .

Remember: A temporal table is two tables with one interface. The current table is fast for transactional queries; the history table is the archive of every previous state. The period columns are the contract: the engine writes them on every update and delete, and the query clause reads them. The retention policy is the only thing that removes history—the engine never does. SQL Server, Db2, and MariaDB implement the SQL:2011 model with different syntax; Oracle implements a similar capability through Flashback. The concept is the same across all of them: stop writing audit triggers, and let the database keep the history.

Was this helpful?

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!