Starting from zero – Temporal tables, part 3

Starting from zero – Temporal tables, part 3

Today I would like to show how to implement temporal table solution when you have an existing table already present in the database. But this will not be all you find in the article. At the end I would like to show you an example where a new column is added to temporal table.
The previous parts can be found here:
Part 1
Part 2

I will not talk about a scenario where a new empty table is created first and then data from existing table are transferred. That is not the preferred way of dealing with temporal tables and in fact that is even worse scenario I can think about.
Let’s do it in the right way. The table I created has the following structure:

CREATE TABLE dbo.ProductsV3
(
ProductId INT IDENTITY NOT NULL PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Number INT NOT NULL,
Price MONEY NOT NULL
)

As you can see this is a regular table without the columns that are necessary to make it a temporal table. First of all let’s load some data to this table. That will emulate the real environment we usually have:

INSERT INTO [dbo].[ProductsV3]([Name],[Number],[Price])
SELECT [Name],[Number],[Price]
FROM [dbo].[ProductsV2]
GO

By the way – the table ProductV2 is a temporal table we have used in last blog post. According to the definition the temporal table contains two tables: the table that holds the current data and the historical table with changed records. The statement above will transfer only the actual data without touching the historical table. That makes sense of course but I was asked about that this week. Here is the query execution plan to make it clear:

According to the Books Online the process of converting a regular table to the temporal one should be done in transaction and should have the following steps:
• Add two non nullable columns of type datetime2 to the existing table. The precision used in the columns is up to you.
o The columns must have default constraint turned on – as we already have data in the table
• Mark the two columns as PERIOD FOR SYSTEM_TIME
• Drop the defaults as SQL Server will take care about managing data in these column.
• Set SYSTEM_VERSIONING to ON and add historical table name or not if you wish the SQL Server does it for you

Here is the code:

BEGIN TRAN;

ALTER TABLE dbo.ProductsV3 ADD
SYSSTART DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL
CONSTRAINT DT_ProductsV3_sysstart DEFAULT('19000101'),
SYSEND DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL
CONSTRAINT DT_ProductsV3_sysend DEFAULT('99991231 23:59:59'),
PERIOD FOR SYSTEM_TIME (SYSSTART, SYSEND);

ALTER TABLE dbo.ProductsV3
DROP CONSTRAINT DT_ProductsV3_sysstart

ALTER TABLE dbo.ProductsV3
DROP CONSTRAINT DT_ProductsV3_sysend

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsV3Hist ) );

COMMIT TRAN;

And that is it! No sweat at all! Now let’s go to the other situation where the table is already a temporal one and we have a new column to be added to the table. I bet you already can imagine that code will not work as long as the ProductsV3 table is a temporal table:

ALTER TABLE dbo.ProductsV3 ADD Colour NVARCHAR(20) NULL

The error message says the same:

Msg 13550, Level 16, State 1, Line 156
Add column operation failed on table 'TemporalDemo.dbo.ProductsV3' because it is not supported operation on system-versioned temporal tables.

Let’s try another way then. Let’s remove the system versioning from the ProductsV3 table, add the column and turn on the system versioning again. I will do that operation in transaction so anything wrong happen in between the whole operation is not accepted:

BEGIN TRAN;

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = OFF)

ALTER TABLE dbo.ProductsV3 ADD
Colour NVARCHAR(20) NULL

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsV3Hist
, DATA_CONSISTENCY_CHECK = ON ) );

COMMIT TRAN;

The error message says:

Msg 13523, Level 16, State 1, Line 168
Setting SYSTEM_VERSIONING to ON failed because table 'TemporalDemo.dbo.ProductsV3' has 7 columns and table 'TemporalDemo.dbo.ProductsV3Hist' has 6 columns.

That leads me to the idea that the new column must be added to both tables in the same transaction. That means that both tables must have exactly the same structure. I hope you can see that I also included a phrase: DATA_CONSISTENCY_CHECK = ON along with the HISTORY_TABLE. This data check just want to find out if the column that is defined as a SysEndTime has greater or at least equal values to the column that is defined as a SysStartTime:
SysEndTime ≥SysStartTime

By the way when the SYSTEM_VERSIONING is going to be turned on the SQL Server checks the following (according the official documentation):
• The number of columns is the same in both the current and the history table.
• The datatypes match for each column between the current and the history table.
• The period columns are set to NOT NULL.
• The current table has a primary key constraint and the history table does not have a primary key constraint.
• No IDENTITY columns are defined in the history table.
• No triggers are defined in the history table.
• If the current table has period columns specified as HIDDEN, the matching columns in the history table are also hidden.
• No foreign keys are defined in the history table.
• No table or column constraints are defined on the history table. However, default column values on the history table are permitted.
• History table is not placed in a read-only filegroup.
• The history table is not configured for change tracking and change data capture.

You might guess that this code will not work, too. I just tried to add column to both tables but the data size in the columns are not equal:

BEGIN TRAN;

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = OFF)

ALTER TABLE dbo.ProductsV3 ADD
Colour NVARCHAR(20) NULL

ALTER TABLE dbo.ProductsV3Hist ADD
Colour NVARCHAR(10) NULL

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsV3Hist, DATA_CONSISTENCY_CHECK = ON ));

COMMIT TRAN;

The error message says:

Msg 13525, Level 16, State 1, Line 202
Setting SYSTEM_VERSIONING to ON failed because column 'Colour' has data type nvarchar(10) in history table 'TemporalDemo.dbo.ProductsV3Hist' which is different from corresponding column type nvarchar(20) in table 'TemporalDemo.dbo.ProductsV3'.

What I also tried but without success is:

BEGIN TRAN;

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = OFF)

ALTER TABLE dbo.ProductsV3 ADD
Colour NVARCHAR(20) NULL

ALTER TABLE dbo.ProductsV3Hist ADD
Colour NVARCHAR(50) NULL

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsV3Hist, DATA_CONSISTENCY_CHECK = ON ));

COMMIT TRAN;

The error message is very similar to the one displayed last time:

Msg 13525, Level 16, State 1, Line 220
Setting SYSTEM_VERSIONING to ON failed because column 'Colour' has data type nvarchar(50) in history table 'TemporalDemo.dbo.ProductsV3Hist' which is different from corresponding column type nvarchar(20) in table 'TemporalDemo.dbo.ProductsV3'.

At the end we have to add the column that way to make sure in both tables this column is identical:

BEGIN TRAN;

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = OFF)

ALTER TABLE dbo.ProductsV3 ADD
Colour NVARCHAR(20) NULL

ALTER TABLE dbo.ProductsV3Hist ADD
Colour NVARCHAR(20) NULL

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsV3Hist, DATA_CONSISTENCY_CHECK = ON ));

COMMIT TRAN;

What to do in case of removing a column from the temporal table? Well you have to follow the same pattern:

BEGIN TRAN;

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = OFF)

ALTER TABLE dbo.ProductsV3 DROP COLUMN Colour

ALTER TABLE dbo.ProductsV3Hist DROP COLUMN Colour

ALTER TABLE dbo.ProductsV3
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsV3Hist, DATA_CONSISTENCY_CHECK = ON ));

COMMIT TRAN;

And the same pattern is also valid when you would like to change the data type of a column.
That is all for today! Stay tuned – in the next blog post I will try to show what happens when you do a SELECT statement on the temporal table.

Cheers
Damian

Previous Last week reading (2018-11-18)
Next ASF 016: Chris Webb interview

About author

You might also like

SQL Server 0 Comments

SQL Server 2022 is coming

SQL Server 2022 is still in private preview (Microsoft MVP folks have access to it), but you can now read about the new release before it is released:  SQL Server

Power BI 0 Comments

How to start with Power BI Desktop

This part of blog is for  a people who never had contact with programming or Power BI. I’m want to show you that is impossible understanding this amazing tools without

Green Zone 2 Comments

Installation and setting up Power BI Gateway

In this short post, I will show you step by step on how to install two different modes of a Power BI Data Gateway. What is it this Data Gateway?

0 Comments

No Comments Yet!

You can be first to comment this post!

Leave a Reply