Temporal table system consistency checks
When using temporal tables, the system performs a number of consistency checks to ensure the schema complies with the requirements for temporal and the data is consistent, and remains consistent. In addition, temporal checks have been added to the DBCC CHECKCONSTRAINTS statement.
System consistency checks
Before SYSTEM_VERSIONING is set to ON, a set of checks are performed on the history table and the current table. These checks are grouped into schema checks and data checks (if history table is not empty). In addition, the system also performs a runtime consistency 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 does not 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 is not placed in a read-only filegroup.
- The history table is not configured for change tracking or change data capture.
Data consistency check
Before SYSTEM_VERSIONING is set to ON and as part of any DML operation, the system performs the following check: SysEndTime ≥SysStartTime
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 do not overlap and that temporal requirements are fulfilled for every individual record. Performing the data consistency check is the default. Generally, performing the data consistency is recommended whenever the data between the current and history tables may be out of sync, such as when incorporating an existing history table that is populated with history data.
Manual changes to the system clock will cause the system to fail unexpectedly because the runtime data consistency checks that are in place to prevent overlap conditions (namely that the end time for a record is not less than its start time) will fail.
The DBCC CHECKCONSTRAINTS command includes temporal data consistency checks. For more information, see DBCC CHECKCONSTRAINTS (Transact-SQL).
- Temporal Tables
- Getting Started with System-Versioned Temporal Tables
- Partitioning 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