systemversioned

Systemversioned, often referred to as system-versioned temporal tables, is a database feature that automatically preserves a full history of data changes over time. In Microsoft SQL Server, system-versioned tables provide built-in support for storing and querying past data without requiring manual audit logic. A base table is configured to keep previous versions in a separate history table, while current rows remain in the base table with a validity period.

Key elements of the implementation include period columns and system time semantics. Two columns, typically created as GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END, define the period during which a row is considered current. These columns are grouped with a PERIOD FOR SYSTEM_TIME clause to establish the temporal dimension. System versioning is enabled with the clause WITH (SYSTEM_VERSIONING = ON), optionally specifying an existing history table or letting the system create one automatically.

Changes to data—such as updates and deletes—result in new versions being stored in the history table, while the base table continues to reflect the most recent state. The history table retains the previous values along with their associated time period. Users can query data using FOR SYSTEM_TIME clauses, including FOR SYSTEM_TIME AS OF, FOR SYSTEM_TIME BETWEEN, and FOR SYSTEM_TIME ALL, enabling point-in-time and interval analysis.

Usage considerations include ensuring a suitable primary key on the base table, choosing a history table strategy, and understanding performance and storage implications. System-versioned databases provide enhanced auditability and data recovery capabilities but introduce overhead and maintenance considerations. Other database systems offer analogous temporal features, such as Oracle’s Flashback and IBM Db2 temporal tables.