A SQL Server feature that automatically maintains a full history of all changes to table rows, enabling point-in-time queries that retrieve the state of data at any previous moment.
-
System-versioned tables: Temporal tables pair a current table with a linked history table. SQL Server automatically stores previous row versions in the history table whenever an
UPDATEorDELETEoperation occurs. -
Time-travel queries: DBAs and developers can query historical data using the
FOR SYSTEM_TIMEclause with options such asAS OF,BETWEEN,FROM...TO,CONTAINED IN, andALLto retrieve data from specific points or ranges in time. - Common use cases: Temporal tables are commonly used for data auditing, regulatory compliance, viewing historical data states, undo and redo operations, slowly changing dimension management, and trend analysis.
- Enterprise integration: Temporal tables integrate with Always On Availability Groups, Row-Level Security, and Dynamic Data Masking, making them well suited for enterprise compliance and security requirements.
- History management: As history tables grow over time, DBAs should configure data retention policies (available in SQL Server 2017 and later) to automatically remove historical data beyond a specified retention period.
- Temporal tables vs. Change Data Capture: Temporal tables automatically maintain a complete history of row versions, while Change Data Capture (CDC) records change events in separate queryable change tables.
- Related terms: Change Data Capture (CDC), Compliance, Dynamic Data Masking, Row-Level Security.
