Edit

Temporal table system consistency checks

  • Article

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 have been added to 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:

  1. The names and number of columns is the same in both the current table and the history table.
  2. The datatypes match for each column between the current table and the history table.
  3. The period columns are set to NOT NULL.
  4. The current table has a primary key constraint and the history table doesn't have a primary key constraint.
  5. No IDENTITY columns are defined in the history table.
  6. No triggers are defined in the history table.
  7. No foreign keys are defined in the history table.
  8. No table or column constraints are defined on the history table. However, default column values on the history table are permitted.
  9. The history table isn't placed in a read-only filegroup.
  10. 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 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. Generally, you should perform the data consistency check 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.

Warning

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.

DBCC CHECKCONSTRAINTS

The DBCC CHECKCONSTRAINTS command includes temporal data consistency checks. For more information, see DBCC CHECKCONSTRAINTS (Transact-SQL).

Next steps