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
About author
You might also like
Stellar Repair for MS SQL – Product Review
If you’re experiencing delays and errors while working with SQL Server databases due to corrupt database files, you may feel frustrated and helpless. Fortunately, Stellar Repair for MS SQL offers
Getting the Data to Power BI
Welcome to the next post regarding Power BI. This time, we will know Power BI from the more pleasurable side – that is, it will be practically and conveniently. Namely,
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.
0 Comments
No Comments Yet!
You can be first to comment this post!