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 will be used later in the demos.  The dbo.ProductV3 table will be a little bit different than the previous two. I will use this table for a different scenario. The previously created tables will be useful when we start with temporal functionality for new tables. But what in case when we need to transform the existing table into temporal table? That is the scenario I would like to test on the table below:

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

Today we will check how to load data into the temporal table (insert statement) and I will point ou t to one very important thing during that process.
Loading data to the temporal table should be pretty easy. If you think about this process a little you know that it should only affect the base table and have zero effect on the historical table. I load one row to the to the ProductsV2 table

INSERT INTO [dbo].[ProductsV2]([Name],[Number],[Price])
VALUES(N'ProductA',123000,5.87)
GO

The query plan is exactly as we might expect. There is a clustered index insert on the base table and the historical table was not touched during the INSERT operation:

If you do a SELECT statement on the ProductV2 table than you will see that one record was inserted (no surprise on that). Please take a look on the two system date columns. The SYSSTART column contains the datetime of the operation and the SYSEND column is set to 9999-12-31 23:59:59.9 in my case. Please bear in mind that the precision of the data type in the column was set during the table creation.

SELECT * FROM dbo.[ProductsV2]
GO

If you do the same select statement against the ProductsV2Hist table than you will see that nothing is in that table so far.
Now let’s load multiple rows into the ProductsV2 table. The question is what will be the value of datetime stored in the SYSSTART column:

INSERT INTO [dbo].[ProductsV2]([Name],[Number],[Price])
VALUES(N'ProductB',223000,1.00),
(N'ProductC',323000,2.00),
(N'ProductD',423000,3.00),
(N'ProductE',523000,4.00),
(N'ProductF',623000,5.00)
GO

The thing I would like to check now is how are the SYSSTART column values handled in the transaction during the data loading process. Let’s take a look at the code:

BEGIN TRANSACTION

PRINT CONCAT('Begin of the transaction: ',SYSDATETIME())

WAITFOR DELAY '00:00:05'



PRINT CONCAT('After the first waiting: ',SYSDATETIME())

INSERT INTO [dbo].[ProductsV2]([Name],[Number],[Price])

VALUES(N'ProductG',723000,10.00)



PRINT CONCAT('The ProductG inserted at: ',SYSDATETIME())

WAITFOR DELAY '00:00:05'



PRINT CONCAT('After the second waiting: ',SYSDATETIME())

INSERT INTO [dbo].[ProductsV2]([Name],[Number],[Price])

VALUES(N'ProductH',823000,11.00)



PRINT CONCAT('The ProductH inserted at: ',SYSDATETIME())



WAITFOR DELAY '00:00:05'

PRINT CONCAT('Commiting transaction: ',SYSDATETIME())



COMMIT

GO

In the first line I start a transaction. Then there is a WAITFOR statement which emulates business logic that for example could be done by a stored procedure. That a record for ProductG is inserted to the table and again we have to wait for 5 second and so on. At the end I just commit the transaction. The question is – what will be the value in the SYSSTART column for each insert operation?

Below you can find the result of the batch execution:

Begin of the transaction: 2018-11-17 08:40:25.5248095

After the first waiting: 2018-11-17 08:40:30.5411411

The ProductG inserted at: 2018-11-17 08:40:30.5411411

After the second waiting: 2018-11-17 08:40:35.5533124

The ProductH inserted at: 2018-11-17 08:40:35.6314396

Commiting transaction: 2018-11-17 08:40:40.6471049

What can we find in the table ProductsV2?

Is that a surprise that both records has the SYSSTART value as it were at the beginning of the transaction? From the other hand we can see that the record were really inserted slightly later. But I am not surprised as the SYSSTART column is the system column so the value entered there is the system datetime.
The last experiment for today is to prove that it is not possible to use the system columns (SYSSTART and SYSEND) while load the data to the table. It makes sense to me as those column are handled by the SQL Server thus cannot be used (modified) directly. In this example I took data from ProductsV2 table and load the into the Products table:

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

The result is as follow:

Msg 13536, Level 16, State 1, Line 100
Cannot insert an explicit value into a system period column in table 'TemporalDemo.dbo.Products'.
Use INSERT with a column list to exclude the system period column, or insert a DEFAULT into the system period column.

We could load the data in this way:

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

The table Products contains 8 rows and the SYSSTART column values are the same but different from the SYSSTART values in the ProductsV2 table. Which is not surprised at all.

That is all for today. The next blog post will be about converting the existing table into a temporal one. I will also explain what could happen in between and how SQL Server checks if we are going to fool him during the conversion process. Stay tuned.

Cheers,

Damian