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 a 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 remove System Versioning? In our previous example on Changing the schema of a Temporal Table.
The following example shows how to completely remove System Versioning from dbo.SalesPerson Table and drop the dbo.SalesPersonHistory Table. We could also want to drop the PERIOD columns.
You can read the Previous Blog post Changing the schema of a Temporal Table
to continue with this series of the Blog Post or you can start from Temporal Table in SQL SERVER 2016.
Note: we cannot drop either the dbo.SalesPerson or the dbo.SalesPersonHistory tables while system versioning is enabled.
Step1: Below code Remove System Versioning from dbo.SalesPerson table, Drop the Period Column From dbo.SalesPerson table and Drop the dbo.SalesPersonHistory table.
| USE SQL2016Demo; GO ALTER TABLE dbo.SalesPerson SET (SYSTEM_VERSIONING = OFF); ALTER TABLE dbo.SalesPerson DROP PERIOD FOR SYSTEM_TIME; DROP TABLE dbo.SalesPersonHistory; |
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
Changing the schema of a Temporal Table.
SQL Skills 