Temporal Table in SQL SERVER 2016. Changing the schema of a Temporal Table.

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.

Temporal Table in SQL SERVER 2016. Migrate An Existing Solution to Use System Versioning.

Note: This topic applies to Microsoft SQL SERVER 2016.

In my previous Blog Post we have learned about how to implement Temporal Table on a new table? For more information on Temporal table please follow Temporal Table in SQL SERVER 2016.

Let’s assume that we have below solution managed via stored Procedure for System Versioning. In this existing solution we have a table called dbo.SalesPerson and if the Sales Person’s region changed then we maintain the history in the table called dbo.SalesPersonHistory.

Step1: First let’s create a Demo Database Called SQL2016Demo.

USE master
Go
— Drop database
IF DB_ID (‘SQL2016Demo’) IS NOT NULL
DROP DATABASE SQL2016Demo;
GO
CREATE DATABASE SQL2016Demo;

Output: Below Image show that SQL2016Demo Database is created.

Image1:

Step2 : In this step we will create a table called dbo.SalesPerson with an assumptions that each sales person has their sales region and their Sales Region sales over the time, when Business change their sales region we need to keep their Historical Sales Regions in a table called dbo.SalesPersonHistory. Here, in this example we have two columns called RowStartDate and RowEndDate to track the changes for the Sales Region for each SalesPerson over the period of time.

USE SQL2016Demo;
GO
CREATE TABLE dbo.SalesPerson
(
SalesPersonId     Int IDENTITY,
SalesPersonName   Varchar (50),
SalesRegion       Varchar (50),
RowStartDate      date NOT NULL Default GetDate (),
RowEndDate        date NOT NULL Default ‘2079-12-31’,
Constraint PK_SalesPerson_SalesPersonId Primary Key(SalesPersonId)
);
GO
CREATE TABLE dbo.SalesPersonHistory
(
SalesPersonId     Int NOT NULL,
SalesPersonName   Varchar (50),
SalesRegion       Varchar (50),
RowStartDate      date NOT NULL,
RowEndDate        date NOT NULL
);
GO
–Populate the dbo.SalesPerson Table with some initial data.
INSERT dbo.SalesPerson(SalesPersonName , SalesRegion , RowStartDate)
Values (‘John Smith’ , ‘Hertfordshire’ , ‘2015-05-01’) ,
       (‘Anthony Webster’ , ‘South West London’, ‘2015-05-01’),
       (‘Matt Williams’ , ‘Kingston upon Thames’, ‘2015-05-01’),
       (‘James Jenkinson’ , ‘Croydon’, ‘2015-05-01’),
       (‘Tom Bailey’ , ‘Slough’, ‘2015-05-01’)
GO

Step3: In this step we are creating a stored procedure to update the Sales Region for the Sales person and keep their historical Sales Region in SalesPersonHistory Table. Please find below T-SQL to create a stored procedure and update the Sales Region for the Sales Person Id 1 and 2.

USE SQL2016Demo;
GO
Create Procedure dbo.usp_UpdateSalesPersonRegion(@SalesPersonid INT, @SalesRegion Varchar(50))
As
Begin
SET NOCOUNT ON;
    UPDATE dbo.SalesPerson SET SalesRegion = @SalesRegion ,
                    RowStartDate = GetDate()
    OUTPUT    inserted.SalesPersonId,
inserted.SalesPersonName,
deleted.SalesRegion,
deleted.RowStartDate,
GetDate()-1 as RowEndDate
INTO dbo.SalesPersonHistory(
SalesPersonId,
SalesPersonName,
SalesRegion,
RowStartDate,
RowEndDate)
Where
SalesPerson.SalesPersonId = @SalesPersonid;SET NOCOUNT OFF;
END
GO
EXEC dbo.usp_UpdateSalesPersonRegion @SalesPersonid = 1,
@SalesRegion = ‘North West London’
GO
EXEC dbo.usp_UpdateSalesPersonRegion @SalesPersonid = 2,
@SalesRegion = ‘Hertfordshire’

Please find below Image2: which show how the records look like in dbo.SalesPerson and dbo.SalesPersonHistory table.

Image2:

So far, I have implemented a solution via stored procedure which maintains system versioning for the sales person’s historical sales regions in to SalesPersonHistory table. Now we are going to learn how we can migrate this existing solution to system versioning or Temporal Table.

How to Migrate Existing Solution to use System Versioning?

Step1:

The first change we need to do is that in our dbo.SalesPerson table maximum date value for RowEndDate is 2079-12-31 but System versioning required it to be the 9999-12-31 otherwise it will throw below error. So let’s update the RowEndDate to as 9999-12-31.

Error: ADD PERIOD FOR SYSTEM_TIME failed because table ” contains records where end of period is not equal to MAX datetime.

USE SQL2016Demo;
GO
UPDATE dbo.SalesPerson SET RowEndDate =
CONVERT (DATETIME2, ‘9999-12-31 23:59:59.9999999’)
GO

Step2: Follow below T-SQL code to migrate existing solution to use System Versioning.

USE SQL2016Demo;
GO
— Drop existing Stored Procedure, we also need to remove this stored procedure from the calling application.
DROP PROCEDURE usp_UpdateSalesPersonRegion;

— Adjust the schema for current and history table
–Drop the Default Constraint from column RowStartDate and RowEndDate On table dbo.SalesPerson;
ALTER TABLE dbo.SalesPerson
DROP CONSTRAINT DF_SalesPerson_RowStartDate;ALTER TABLE dbo.SalesPerson
DROP CONSTRAINT DF_SalesPerson_RowEndDate;— Change data types for existing period columns
ALTER TABLE dbo.SalesPerson
ALTER COLUMN [RowStartDate] datetime2 NOT NULL;ALTER TABLE dbo.SalesPerson
ALTER COLUMN [RowEndDate] datetime2 NOT NULL;

ALTER TABLE dbo.SalesPersonHistory
ALTER COLUMN [RowStartDate] datetime2 NOT NULL;
ALTER TABLE dbo.SalesPersonHistory
ALTER COLUMN [RowEndDate] datetime2 NOT NULL;

— Add SYSTEM_TIME period and set system versioning with linking two existing tables — (a certain set of data checks happen in the background)

ALTER TABLE dbo.SalesPerson
ADD PERIOD FOR SYSTEM_TIME ([RowStartDate], [RowEndDate])

ALTER TABLE dbo.SalesPerson
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SalesPersonHistory, DATA_CONSISTENCY_CHECK = ON))

Temporal Table in SQL SERVER 2016: How to Implement Temporal Table on a New table?

Note: This topic applies to Microsoft SQL SERVER 2016.

In my Previous Blog post Temporal Table in SQL SERVER 2016 we have learnt about Temporal Table which Microsoft has introduced in SQL SERVER 2016. In this Blog post we will be learning how to implement Temporal Table on a new table?

Step1: First let’s create Demo Database Called SQL2016 Demo.

Output: Observer Image1, Demo Database is created.

Image1:

Step2: In step we will implement Temporal Table or System Versioned table called Department without specifying History_Table option. In below transact SQL we haven’t use HISTORY_TABLE = [History_Table_Name] option, we have just use WITH (SYSTEM_VERSIONING = ON) option. In step 3 I will explain you how to use HISTORY_TABLE option.

Output: With the help of above transact SQL we have implemented a temporal table or System-Versioned table called Department and as we have not specified historical table name so system has created a table called dbo.MSSQL_TemporalHistoryFor_885578193 (History) highlighted in yellow in below Image2. Also History table is underneath the current table. In ideal world it is not easy to remember or workout which table is the history table for Department or any Temporal Table if the table name is like MSSQL_TemporalHistoryFor_%, so in next step we will be implementing Temporal Table with proper history table name?

The Department table has also period definition column called SysStartTime and SysEndTime of datetime2 data type. Please observe in blue circle in below image Image2.

Image2:

Step3: In step we will implement Temporal Table or System Versioned table called Department1 with specifying History_Table option. In below transact SQL we use HISTORY_TABLE = [Department1_History] option. Here in this step I will creating table called Department1 instead of Drop the existing table called Department. To drop table system versioned table, it requires to disable the system versioning on the table then we can drop the table. I will explain how to disable the System version on the table and Drop table or change the schema later on.

Output: So, in this step we implemented a Temporal Table or System-Versioned table called Department and we have specified historical table name called Department1_History so the system has created table called dbo.Department1_Histsory (History) highlighted in yellow in below Image3. Also History table is underneath the current table.

Image3:

So, in this Blog post we learnt how to implement Temporal Table or System versioned table on a new table, with specifying History_Table option and without specifying History_Table option.

Related Post

Temporal Table in SQL SERVER 2016

Migrate an Existing Solution to Use System Versioning.