Edit

Stopping system-versioning on a system-versioned temporal table

  • Article

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

You may want to stop versioning on your temporal table either temporarily or permanently. You can do that by setting the SYSTEM_VERSIONING clause to OFF.

Setting SYSTEM_VERSIONING = OFF

Stop system-versioning if you want to perform specific maintenance operations on a temporal table or don't need a versioned table anymore. Because of this operation, you get two independent tables:

  • Current table with a period definition

  • History table as a regular table

Important remarks

  • History Table stops capturing the updates during SYSTEM_VERSIONING = OFF.
  • No data loss happens on the temporal table when you set SYSTEM_VERSIONING = OFF or drop the SYSTEM_TIME period.
  • When you set SYSTEM_VERSIONING = OFF and don't remove drop the SYSTEM_TIME period, the system continues to update the period columns for every insert and update operation. Deletes on the current table are permanent.
  • Drop the SYSTEM_TIME period to delete the period columns.
  • When you set, SYSTEM_VERSIONING = OFF, all users with sufficient permissions can modify the schema and content of the history table or even permanently delete the history table.
  • You can't set SYSTEM_VERSIONING = OFF if you have other objects created with SCHEMABINDING using temporal query extensions - such as referencing SYSTEM_TIME. This restriction prevents these objects from failing if you set SYSTEM_VERSIONING = OFF.

Permanently remove SYSTEM_VERSIONING

This example permanently removes SYSTEM_VERSIONING and deletes the period columns. Removing the period columns is optional.

ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF);
/*Optionally, DROP PERIOD if you want to revert temporal table to a non-temporal*/
ALTER TABLE dbo.Department
DROP PERIOD FOR SYSTEM_TIME;

Temporarily remove SYSTEM_VERSIONING

This is the list of operations that requires system-versioning to be set to OFF:

  • Removing unnecessary data from history (DELETE or TRUNCATE)
  • Removing data from current table without versioning (DELETE, TRUNCATE)
  • Partition SWITCH OUT from the current table
  • Partition SWITCH IN into the history table

This example temporarily stops SYSTEM_VERSIONING to allow you to perform specific maintenance operations. If you stop versioning temporarily as a prerequisite for table maintenance, we strongly recommend doing this inside a transaction to keep data consistency.

Note

When turning system versioning back on, don't forget to specify the HISTORY_TABLE argument. Failing to do so results in a new history table being created and associated with the current table. The original history table can still exist as a normal table but won't be associated with the current table.

BEGIN TRAN
ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF);
TRUNCATE TABLE [History].[DepartmentHistory]
WITH (PARTITIONS (1,2))
ALTER TABLE dbo.Department SET
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.DepartmentHistory)
);
COMMIT ;

Next steps