Part 1 – How to create Temporal tables
Part 2 – Accessing History data from Temporal Tables
In this post we will how to modify Temporal Tables, How to disable System Versioning in Temporal tables to update large set data of without affecting history or truncate tables
Modifying Temporal Tables
For adding new columns to Temporal tables, Please use below command
ALTER TABLE dbo.Demo ADD DemoDesc VARCHAR(255) NULL
Screenshot before adding the new column
Screenshot after adding the new column – If we notice, adding the column in the main table automatically modifies the history table as well
Similarly we can modify the tables to drop column or increasing the size of the field, but some of the changes to table are not supported ex. Adding Identify column or changing the data type of the field which is not supported using implicit conversion, in that scenario we need to disable the system versioning to make changes
Disable System Versioning
Screenshot before disabling system versioning
Script to disable system versioning
ALTER TABLE dbo.Demo SET (SYSTEM_VERSIONING = OFF);
Screenshot after disabling system versioning – If we notice the System versioning is disabled and both Main and history table is displayed as regular tables now
There are other scenarios we need to disable System versioning, if we need to do any of the following operations
1. TRUNCATE TABLE on Main or History table
Msg 13545, Level 16, State 1, Line 1
Truncate failed on table ‘SQL2017_DEMO.dbo.Demo’ because it is not a supported operation on system-versioned tables.
2. Deleting rows from History table
Msg 13560, Level 16, State 1, Line 4
Cannot delete rows from a temporal history table ‘SQL2017_DEMO.dbo.DemoHistory’.
3. Dropping Main or History table
Msg 13552, Level 16, State 1, Line 6
Drop table operation failed on table ‘SQL2017_DEMO.dbo.Demo’ because it is not a supported operation on system-versioned temporal tables.
Hope you find this post helpful !!!
2 thoughts on “Temporal Tables – Simple Guide to implement History Tables using SQL Server 2016/2017–Part 3–Modifying Temporal Tables, Disabling System Versioning and why we need to disable”