The System Consistency feature in Temporal tables

Introduction: 

At the time of creating temporal tables, SQL Server software performs various checks to ensure the compatibility of their structure and data with the table's conditions. Before enabling SYSTEM_VERSIONING, a set of controls is applied to both the main table and the history table to ensure the compatibility of their data and structure. These controls occur if the history table is not empty and, depending on the configuration of this feature, can also be performed online and at runtime.

Structural controls:

If the primary and history tables exist and we want to link them together or make any changes to the structure of the primary table, the following default controls are applied:

- The names and number of columns in both tables must exactly match.

- The data types for all columns in both tables must exactly match.

- The columns representing the start and end of changes, mentioned in the Period section, must be of the NOT NULL type.

- The primary table must have a primary key, and the history table must not have a primary key.

- Columns of the IDENTITY type should not exist in the history table.

- No triggers, foreign keys, or constraints should be defined on the history table, except for a default constraint, which is allowed.

- The history table should not be placed in a read-only file group.

- CDC and Change tracking features cannot be enabled on the history table.

Data Consistency Check feature: 

When we intend to change the SYSTEM_VERSIONING feature to ON, before making the change, the database management system, in addition to the above controls and in the form of DML commands, checks the columns related to the start and end times of each record. If the ValidFrom column represents the start time and the ValidTo column represents the end time, the condition ValidTo >= ValidFrom is checked to ensure there is no overlap between records. If any inconsistency or overlap exists, creating the table encounters an error. Manual changes in the values of these two columns or changes in the system time can lead to problems in such tables. These types of issues can be identified through DBCC CHECKCONSTRAINTS. It is worth mentioning that this control can be very time-consuming, especially for historical tables with a high number of records.

 Scenario:

Let's consider a scenario where we have a System-Versioned table with approximately 50 million records in the main table and about 100 million records in the history table. Due to certain reasons, we intend to delete all records from the main table. Time is of the essence for us in performing this task, and we aim to do it with minimal time, cost, and overhead. Additionally, we do not need to retain the deleted data in the history table.

Solution:

First, you need to turn off the System-version.To do this, you use the following command.

ALTER TABLE YourTableName SET (SYSTEM_VERSIONING = OFF )

Then, using the TRUNCATE command, you can delete the data from the main table in a fraction of a second.

TRUNCATE TABLE YourTableName

Finally, we need to re-establish the relationship between the main table and the history table. At this point, if we were to use the Alter command by default to enable the System_version feature, SQL Server would start examining all records, which for a table of this size would be very time-consuming. Therefore, to prevent the DATA CONSISTENCY CHECK process, assuming that we consciously intend to link these two tables and are aware of the potential data corruption, we need to use the DATA_CONSISTENCY_CHECK = OFF option when activating the SYSTEM_VERSION feature. In this case, SQL Server no longer checks the date condition for records and quickly establishes the relationship between the two tables.

SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.YourHistoryTableName, DATA_CONSISTENCY_CHECK = OFF))

Tip: 

- Using the DATA_CONSISTENCY_CHECK = OFF option during the creation or alteration of Temporal Tables can conceal existing data inconsistencies. Therefore, it is recommended to use this option only in exceptional circumstances and with full awareness of its implications. It is preferable to let this feature remain ON by default, allowing SQL Server to perform all necessary checks.

- If any issues arise in these tables due to reasons such as a change in the system clock, you can temporarily keep the table active by disabling this feature until the inconsistencies are addressed and the problem is resolved.