Contents
Can you stop SYSTEM VERSIONING on a temporal table?
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.
What happens when you turn off versioning in SQL?
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.
How to drop the temporal table in SQL Server?
You must first disable versioning, which will cause the history table to become an ordinary table. Then you can drop both the temporal table and its corresponding history table. ALTER TABLE [dbo]. [TemporalTest] SET ( SYSTEM_VERSIONING = OFF ) GO DROP TABLE [dbo]. [TemporalTest] GO DROP TABLE [dbo]. [TemporalTestHistory] GO
How to create a temporal table with default history table?
Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on the system to create the history table with the default configuration. In the example below, a new table is created with system-versioning enabled with the name of the history table explicitly defined.
How to enable SYSTEM VERSIONING in SQL Server?
For each table to which we want to add system versioning, we need to: 1 add two columns that will store the period of validity for each row; 2 define a period for the table based on the two columns above; 3 if there isn’t one already, add a primary key; 4 enable versioning and define the name of the history table.
What happens when you SET SYSTEM VERSIONING = off?
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 .
Can You Drop a temporal table in DBO?
Drop table operation failed on table ‘TestAudit.dbo.SomeData’ because it is not supported operation on system-versioned temporal tables. As the message says, you can’t just drop Temporal tables. First you must turn system versioning off, then you can go ahead: