Note: This topic applies to Microsoft SQL SERVER 2016.
So far we have covered new feature introduced by Microsoft in SQL SERVER 2016 called Temporal Table, which will be useful for system version of the table. Then I have covered How to implement temporal table on a new table or Creating a new Temporal Table. You can find more information if we have to Migrate existing solution to use system versioning or Temporal Table or System Versioned table.
In this Blog Post, we will be learning on How to change the schema of a Temporal Table in the scenario where we need to change the schema of Temporal Table? In our previous example on Migrate existing solution to use system versioning or Temporal Table or System Versioned table, we were maintaining Sales Person’s Region in dbo.SalesPerson and dbo.SalesPersonHistory table. Now business want to track each Sales Person’s Area Sales Manager, which requires a schema changes on a Temporal Table to add a new column called AreaSalesManager, so let’s learn how we can alter the structure of a Temporal table.
Schema changes are blocked while SYSTEM_VERSIONING is ON to prevent the two table becoming out of sync, so in order to change the schema of a temporal table we must first temporarily disable the SYSTEM_VERSIONING by setting it as SYSTEM_VERSIONING = OFF. While SYSTEM_VERSIONING is OFF we can make changes to the both tables independently and then re-enable the SYSTEM_VERSIONING. Perform the above steps within a transaction to maintain data consistency.
The following example demonstrated this technique. You can read the Previous Blog post Migrate existing solution to use system versioning or Temporal Table or System Versioned table
to continue with this series of the Blog Post.
Step1: Below code implements schema changes in Transaction.
| BEGIN TRAN /* Takes schema lock on both tables */ ALTER TABLE dbo.SalesPerson SET (SYSTEM_VERSIONING = OFF) /* add column to current table */ ALTER TABLE dbo.SalesPerson ADD AreaSalesManager Varchar (50) NULL; /* add column to history table */ ALTER TABLE dbo.SalesPersonHistory ADD AreaSalesManager Varchar (50) NULL; /* Re-establish versioning again given that this operation is under strict control (other transactions are blocked), DBA chooses to ignore data consistency check in order to make it fast*/ ALTER TABLE dbo.SalesPerson SET (SYSTEM_VERSIONING =ON (HISTORY_TABLE=dbo.SalesPersonHistory, DATA_CONSISTENCY_CHECK = OFF)); COMMIT; GO |
Output: Below Image shows that we have successfully added new column to the dbo.SalesPerson and dbo.SalesPersonHistory tables.
Related Post
Temporal Table in SQL SERVER 2016
How to Implement Temporal Table on a New Table?
Migrate existing solution to use system versioning or Temporal Table or System Versioned table
SQL Skills 