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

Question

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?

Does this answer your question? [Cannot delete rows from a temporal history table](https://stackoverflow.com/questions/53746197/cannot-delete-rows-from-a-temporal-history-table) — Michael Freidgeim, Oct 19 '21 at 21:31

score 19 · Accepted Answer · answered May 04 '17 at 10:56

19

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

answered May 04 '17 at 10:56

Aiyoub A.

5,261
8
25
38

1

Hi @Aiyob , Do we need to use the statement - DATA_CONSISTENCY_CHECK = ON here and is it require, – Abdul Azeez Aug 10 '18 at 16:33
The option DATA_CONSISTENCY_CHECK = ON is not required. It is the default and performs various checks before turning the historization back on. Here is the corresponding SQL Docs documentation by Microsoft https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-system-consistency-checks?view=sql-server-ver16. – krystof236 Nov 21 '22 at 17:15

sarin · Answer 2 · 2023-05-22T07:58:17.137

9

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.

edited May 22 '23 at 07:58

answered May 04 '17 at 10:50

sarin

5,227
3
34
63

1

This creates temporary table and does not use previous history table!!! – Dixtosa Oct 28 '21 at 20:47
1

Adding to @Dixtosa comment, this answer does not specity the "HISTORY_TABLE" argument, and thus another table is created. From Microsoft docs "When turning system versioning back on, don't forget to specify the HISTORY_TABLE argument. Failing to do so results in a new history table being created and associated with the current table. The original history table can still exist as a normal table but won't be associated with the current table." https://learn.microsoft.com/en-us/sql/relational-databases/tables/stopping-system-versioning-on-a-system-versioned-temporal-table?view=sql-server-ver16 – JimbobTheSailor May 21 '23 at 07:20

score 1 · Answer 3 · answered May 08 '22 at 07:23

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.

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

3 Answers3