How to modify system-versioned temporal table schema in SQL Server

In SQL Server, Temporal Table is used to keep the full history of data change for table without doing any extra coding or efforts. It will allow to you get table data at any point of time of past. For more information about SQL Server temporal tables, please visit temporal table in SQL Server .

If you want to modify (add or modify or delete column) the existing temporal table schema, you need to make sure history table schema should be sync.

Here are steps how to add new column ‘ZipCode’ in system-version temporal table.

1.   Turn off System Versioning

ALTER TABLE [dbo].[Office] SET (SYSTEM_VERSIONING = OFF);

2.   Modify the existing both Tables schema ( Office and OfficeHistory )

ALTER TABLE [dbo].[Office] ADD ZipCode varchar(10) NULL;

ALTER TABLE [dbo].[OfficeHistory] ADD ZipCode varchar(10) NULL;

3.   If data update is needed in history table, do it

Update [dbo].[OfficeHistory] SET ZipCode = '43212' where City = 'NYK'

4.   Turn on System Version option

        ALTER TABLE [dbo].[Office]

        SET

         (

            SYSTEM_VERSIONING = ON

           ( HISTORY_TABLE = [dbo].[OfficeHistory])

         );

Tables:

CREATE TABLE dbo.OfficeHistory(

     OfficeID int NOT NULL 

   , OfficeName varchar(50) NOT NULL

   , Street   varchar(50) NOT NULL

   , City varchar(50) NOT NULL

   , State varchar(2) NOT NULL

   ,SysStartTime   datetime2  NOT NULL,

   ,SysEndTime datetime2  NOT NULL,

)

CREATE TABLE Office  

(   

    OfficeID int NOT NULL PRIMARY KEY CLUSTERED

   , OfficeName varchar(50) NOT NULL

   , Street   varchar(50) NOT NULL

   , City varchar(50) NOT NULL

   , State varchar(2) NOT NULL

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)  

WITH   

   (  

      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OfficeHistory)  

   )

Other blogs related to Temporal Table:

SQL - How to create temporal table in SQL Server

SQL - How to truncate system version temporal table

SQL : how to create temporal table in sql server 2016

In SQL Server 2016, Microsoft introduced a new feature Temporal Table. by using this feature, we can easily keep the full history of data change for table without doing any extra coding or efforts. It will allow to you get table data at any point of time of past.

Temporal table have same number of fields as Regular/Main tables and have two new extra columns start and end date.

1. If you update a record in main table, the old version of record will be added into history table with start and current end date stamp. 2. If you delete a record from main table, the deleted record will be added into history table with start and current end date stamp.

Temporal table serves many purposes : Audit : temporal table is being used to store the data history of main table and you can retrieve the old version of data on given specific date time. Back-up : temporal table helps you to restore the accidentally deleted or updated record.

Syntax to Create Temporal Table :

There are many options available to create temporal table.

1. Auto-generated  name: you can create a temporal table without specify schema and table name and system create the corresponding history table with auto-generated name.

CREATE TABLE Office  

(   

OfficeID int NOT NULL PRIMARY KEY CLUSTERED

  , OfficeName varchar(50) NOT NULL

  , Street   varchar(50) NOT NULL

  , City varchar(50) NOT NULL

  , State varchar(2) NOT NULL

  , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

  , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)   

WITH (SYSTEM_VERSIONING = ON)  

2. User defined name : you can create a temporal table with use defined schema and table name.

CREATE TABLE Office  

(   

OfficeID int NOT NULL PRIMARY KEY CLUSTERED

  , OfficeName varchar(50) NOT NULL

  , Street   varchar(50) NOT NULL

  , City varchar(50) NOT NULL

  , State varchar(2) NOT NULL

  , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

  , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)  

WITH   

  (  

     SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OfficeHistory)  

  )

Insert new record in Office table :

Insert dbo.office(officeID, OfficeName, Street, City, State)

Select 1, 'NYK', '1001 Street RD', 'NYK', 'OH'

Select * from dbo.office

SELECT *  FROM [dbo].[OfficeHistory]

No Record available in history table

Update Office Street Address :

Update dbo.office SET Street = '1999 PTS Street' where OfficeName = 'NYK'

Select * from dbo.office

SELECT *  FROM [dbo].[OfficeHistory]

you can see old version of office record in history table with start and end time stamp.

Delete Office Street Address :

 Delete from dbo.office where OfficeName = 'NYK'

 Select * from dbo.office

 No Record available in office table

  SELECT *  FROM [dbo].[OfficeHistory]

In history table, you can see two old version of record for same office ( office id – 1)