Temporal table system consistency checks
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
With temporal tables, the system performs several consistency checks to ensure the schema complies with the requirements for temporal and the data is consistent, and remains consistent. In addition, temporal checks are available in the DBCC CHECKCONSTRAINTS
statement.
System consistency checks
Before SYSTEM_VERSIONING
is set to ON
, a set of checks is performed on the history table and the current table. These checks are grouped into schema checks and data checks (if history table isn't empty). In addition, the system also performs a runtime consistency check.
Schema check
When creating or alter a table to become a temporal table, the system verifies that requirements are met:
The names and number of columns is the same in both the current table and the history table.
The datatypes match for each column between the current table and the history table.
The period columns are set to
NOT NULL
.The current table has a primary key constraint and the history table doesn't have a primary key constraint.
No
IDENTITY
columns are defined in the history table.No triggers are defined in the history table.
No foreign keys are defined in the history table.
No table or column constraints are defined on the history table. However, default column values on the history table are permitted.
The history table isn't placed in a read-only filegroup.
The history table isn't configured for change tracking or change data capture.
Data consistency check
Before SYSTEM_VERSIONING
is set to ON
and as part of any data manipulation language (DML) operation, the system performs the following check: ValidTo >= ValidFrom
When creating a link to an existing history table, you can choose to perform a data consistency check. This data consistency check ensures that existing records don't overlap and that temporal requirements are fulfilled for every individual record. Performing the data consistency check is the default. You should perform the data consistency check whenever the data between the current and history tables might be out of sync. For example, when incorporating an existing history table that is populated with history data.
Warning
Manual changes to the system clock will cause the system to fail unexpectedly, because the runtime data consistency checks to prevent overlap conditions (namely that the end time for a record isn't less than its start time) fail.
Use DBCC CHECKCONSTRAINTS
The DBCC CHECKCONSTRAINTS
command includes temporal data consistency checks. For more information, see DBCC CHECKCONSTRAINTS.
Related content
- Temporal tables
- Get started with system-versioned temporal tables
- Partition with temporal tables
- Temporal table considerations and limitations
- Temporal table security
- Manage retention of historical data in system-versioned temporal tables
- System-versioned temporal tables with memory-optimized tables
- Temporal table metadata views and functions