Stopping system-versioning on a system-versioned temporal table
You may want to stop versioning on your temporal table either temporarily or permanently. You can do that by setting SYSTEM_VERSIONING clause to OFF.
Setting SYSTEM_VERSIONING = OFF
Stop system-versioning if you want to perform specific maintenance operations on temporal table or if you don't need a versioned table anymore. As a result of this operation you will get two independent tables:
Current table with period definition
History table as a regular table
- History Table will stop caturing the updates for the duration of SYSTEM_VERSIONING = OFF.
- No data loss happens on the temporal table when you setSYSTEM_VERSIONING = OFF or drop the SYSTEM_TIME period.
- When you set SYSTEM_VERSIONING = OFF and do not remove drop the SYSTEM_TIME period, the system will continue to update the period columns for every insert and update operation. Deletes on current table will be permanent.
- Drop the SYSTEM_TIME period to remove the period columns completely.
- When you set SYSTEM_VERSIONING = OFF, all users that have sufficient permissions will be able to modify schema and content of history table or even to permanently delete the history table.
- You cannot 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 removes the period columns completely. 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 current table
- Partition SWITCH IN into 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.
When turning system versioning back on, do not forget to specify the HISTORY_TABLE argument. Failing to do so will result in a new history table being created and associated with the current table. The original history table will 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 ;
- Temporal Tables
- Getting Started with System-Versioned Temporal Tables
- Manage Retention of Historical Data in System-Versioned Temporal Tables
- System-Versioned Temporal Tables with Memory-Optimized Tables
- Creating a System-Versioned Temporal Table
- Modifying Data in a System-Versioned Temporal Table
- Querying Data in a System-Versioned Temporal Table
- Changing the Schema of a System-Versioned Temporal Table