How to Stopping System-Versioning on a System-Versioned Temporal Table in SQL Server 2016?

sql-server sql-server-2016 temporal-tables
Aiyoub picture Aiyoub · May 4, 2017 · Viewed 7.5k times · Source

I have a table that has system versioning (temporal table), but I can not see a design environment visually. I do it because I can see the SYSTEM_VERSIONING clause has been used. I would like to have temporarily Stop and then enable it. Who can advise me?

Answer

Aiyoub picture Aiyoub · May 4, 2017

My problem was solved when i using following query:

-- SET SYSTEM_VERSIONING TO OFF
ALTER TABLE [dbo].[MyTable]
SET (SYSTEM_VERSIONING = OFF)
GO

** Do what ever you want to **

-- SET SYSTEM_VERSIONING TO ON
ALTER TABLE [dbo].[MyTable]
SET 
    (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTable_Archive] , DATA_CONSISTENCY_CHECK = ON ))
GO

Related questions

How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
SQL Server: Filter output of sp_who2

Under SQL Server, is there an easy way to filter the output of sp_who2? Say I wanted to just show rows for a certain database, for example.

Read More
How do I move a table into a schema in T-SQL

I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.

Read More