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:

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:

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:

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:

The error message says the same:

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:

The error message says:

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:

The error message says:

What I also tried but without success is:

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

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

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

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.


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

Warning: count(): Parameter must be an array or an object that implements Countable in /homepages/43/d674154319/htdocs/app674669209/wp-content/themes/trendyblog-theme/includes/single/post-tags-categories.php on line 7

About author

You might also like

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?

Green Zone 0 Comments

Starting from zero – Temporal tables, part 1

The ability to query and make use of historical data is very important for the end users. We are obliged to keep the historical data for some period of time.

Green Zone 0 Comments

Starting from zero – Temporal tables, part 2

This article is the second one in a series. You can find part one on our blog. This time I would like to start with defining one more table that


No Comments Yet!

You can be first to comment this post!

Leave a Reply