CODEWITHSUNDEEP
sql-serversql-server-2016temporal-tables
Aiyoub A.
Aiyoub A.

Reputation: 5391

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

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?

Upvotes: 10

Views: 25161

Answers (3)

Dai
Dai

Reputation: 155726

It seems that after doing SET( SYSTEM_VERSIONING = OFF ), the PERIOD FOR option on the table is also reset.

So this works for me:

ALTER TABLE dbo.MyTable
    SET (
        SYSTEM_VERSIONING = OFF
    )

GO

/* Do stuff here. */

ALTER TABLE dbo.MyTable
    ADD PERIOD FOR SYSTEM_TIME ( ValidFrom, ValidTo );

ALTER TABLE dbo.MyTable
    SET (
        SYSTEM_VERSIONING = ON (
            HISTORY_TABLE          = dbo.MyTable_History,
            DATA_CONSISTENCY_CHECK = ON
        ));

I assume it's possible to combine the last two commands into a single ALTER TABLE statement but I haven't found the magic incantation yet.

Upvotes: 2

sarin
sarin

Reputation: 5307

ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = OFF);   

** Do what ever you want to **

ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTable_Archive]));

See this Microsoft article for more info

UPDATED 22/05/2023 : Added HISTORY_TABLE argument to keep the answer relevant. When the answer was originally written, this was a new feature and generally not required.

Upvotes: 9

Aiyoub A.
Aiyoub A.

Reputation: 5391

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

Upvotes: 24

Related Questions