This blog explains how to truncate
the system version enabled table (i.e. temporal table) and how to Stop
System-Versioning on a System-Versioned Temporal Table
Msg 13545, Level 16, State 1, Line 21
When you will truncate or perform
any specific DML operations pn the temporal table eg. dbo. Project, you will
get above error message
truncate table dbo.Project
Before performing any DML operation
on temporal table, first you need to disable system–versioned table.
To disable system–versioned table,
you simply use SYSTEM_VERSIONING
= OFF option and after that history table will stop capturing the
updates
To truncate the the system version
enabled table, you need to follow below steps
-- Disable the system version
table
--Truncate data
-- Enable the system version
table
ALTER TABLE dbo.Project SET ( SYSTEM_VERSIONING = ON );
Simply you off system-versioning (SYSTEM_VERSIONING = OFF) and you can remove unnecessary data
from temporal history table or temporal table without doing versioning of data
Other blogs related to Temporal
Table:
2 comments:
When setting VERSIONING back ON, be sure to specify the HISTORY table to be sure it matches the previous version. Do this so that any queries already written again the HISTORY don't break if MSSQL decides to create a table name different from the original.
For future visitors - I think this can be summed up like the following:
BEGIN TRAN
ALTER TABLE [dbo].[TABLE] SET (SYSTEM_VERSIONING = OFF);
Truncate Table [dbo].[TABLE]
-- Truncate Table [dbo].[TABLEHISTORY] -- Only if you want to Truncate the history table!
ALTER TABLE [dbo].[TABLE]
SET
(
SYSTEM_VERSIONING = ON
( HISTORY_TABLE = [dbo].[TABLEHISTORY])
);
COMMIT;
Post a Comment