How to Disable Temporal Behavior or System-Versioning on SQL Server Tables

You may need to suspend the system versioning temporarily or disable the temporal behavior completely if it is no longer needed. Let us see all ins and outs of this.

Consider the following table as an example –

CREATE TABLE dbo.TemporalTest(
      ID			INT				NOT NULL	IDENTITY (1, 1) CONSTRAINT PKC_TemporalTest PRIMARY KEY CLUSTERED 
    , Column1		VARCHAR (255)	NOT NULL
    , Column2		VARCHAR (255)	NOT NULL
    , Column3		BIT				NOT NULL					CONSTRAINT DF_TemporalTest_Column3 DEFAULT(0)
    
	, ValidFrom		DATETIME2 (7) GENERATED ALWAYS AS ROW START	NOT NULL
    , ValidTo		DATETIME2 (7) GENERATED ALWAYS AS ROW END	NOT NULL
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH 
(
	SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTest_History)
)
GO

Notice the clock around the table in object explorer and its associated history table –

To disable the versioning on this, simply run the following command to turn it off –

ALTER TABLE dbo.TemporalTest 
SET 
(	
	SYSTEM_VERSIONING = OFF
)

Notice the table in object explorer again and you will see that system-version tag has been removed from it and the associated history table has also become an independent table. They are no longer linked now –

But note, this has just disabled the history so that you can perform any of the operational stuff on the table like removing some history etc. but the fields required for temporal behavior “ValidFrom” and “ValidTo” in our table example are still there and the database engine keep on updating them with each insert and update though no history is maintained for the delete operations. If you want to completely switch off the temporal behavior so that not even these columns are being updated, run the following command –

ALTER TABLE dbo.TemporalTest 
	DROP PERIOD FOR SYSTEM_TIME

Dropping the SYSTEM_TIME is optional to disable the versioning.

If you need to enable the system-versioning again, you can do so simply by running the following command –

ALTER TABLE dbo.TemporalTest 
SET 
(
	SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTest_History)
)

To remember the syntax easily, just compare it with the syntax used at the time of table creation. The only difference is that “WITH” keyword has been replaced with “SET” keyword. If the history table already exists then it would be used provided that it matches the schema, otherwise, it will be created.

Also, note that if you don’t specify the History_TABLE as part of above syntax, a new table will be created by database engine with its own naming convention so it’s important to consider if you want to go clean or let the system handle this. e.g. –

ALTER TABLE dbo.TemporalTest 
SET 
(
	SYSTEM_VERSIONING = ON --(HISTORY_TABLE = dbo.TemporalTest_History)
)

Now, see the table in object explorer and notice the name of history table –

Also, be aware that if system-versioning is already disabled on the table and if you try to disable it again, this will end up with an error –

No data loss happen when you disable the system-versioning or even when you drop the SYSTEM_TIME from the table.