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.
Thanks for this blog. I have a scenario that a temporal version table was created in the master database and when we tried to turn system-versioning=OFF if fails and we can’t even drop the tables. Any idea on how to drop system versioned tables created in the master database?
There is no difference in how temporal tables are created, modified or dropped in a user database or master database. You can disable the system-versioning just like it was another database. It’s hard to further comment on your issue without seeing the error message. However, you may check that you would need CONTROL permissions to modify the behavior.