SQL Server 2016: Temporal tables. Comments on behavior

In this post I would like to highlight couple of behavioral aspects of temporal tables which was observed during test drives. Firstly, is a demonstration of  how temporal tables queried by exposing live query statistics of the SELECT. The other item is about how system versioning tracks updates for cases when values are really not changed.

Disclaimer

The highlighted results are based on pre-release version. Behavior described here is a subject for possible change. Version of SQL Server:

-- Microsoft SQL Server 2016 (CTP2.1) - 13.0.300.44 (X64)
SELECT @@VERSION

 

Query plan of the SELECT and Live Query Statistics

New SQL Server Management Studio feature, called Live Query Statistics, used to capture real flow of query execution. Following animated GIF file shows that current and history data retrieved sequential way within a query, even if query plan uses parallelism.

The picture is clickable and overall duration is about 20 seconds:

01.  Query plan of the SELECT. Live Query Execution
01. Query plan of the SELECT. Live Query Execution. Duration: 20 seconds

This behavior is not actually an issue, however it shows possible room for improvement.

False updates or when column values are updated, but keep still original values

During tests and benchmarks I noticed default behavior, which can be really undesirable in some scenarios – all updates are logged to a history table, even if values were updated to the same values. Short demo:

Data seeding:
CREATE TABLE dbo.TestUpdates
(
 ID int identity primary key
, ColumnInt1 int not null
, ColumnInt2 int not null
, ColumnString3 varchar(50) not null
)
GO

-- Enable system versioning
BEGIN TRAN
ALTER TABLE dbo.TestUpdates
ADD PERIOD FOR SYSTEM_TIME (Valid_From, Valid_Till),
Valid_From datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(),
Valid_Till datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CONVERT(DATETIME2, '9999.12.31');
ALTER TABLE dbo.TestUpdates SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TestUpdates_History, DATA_CONSISTENCY_CHECK = OFF))
COMMIT TRAN

-- Seed dummy row
INSERT INTO TestUpdates (ColumnInt1, ColumnInt2, ColumnString3) 
VALUES (1, 2, 'A')

 

Series of updates
-- Update by the same value, however, different type
UPDATE dbo.TestUpdates SET ColumnInt2 = CAST(2 as FLOAT)

-- Update by the same value
UPDATE dbo.TestUpdates SET ColumnInt1 = 1

-- Update by the same value, alternative variant
UPDATE dbo.TestUpdates SET ColumnInt1 = ColumnInt1

 

End result
SELECT * FROM [dbo].[TestUpdates]
FOR SYSTEM_TIME FROM '20000101' TO '99991231'
01. Result of updates
02. Result of updates

I believe, such result is not always desirable and system versioning should have an option, like KEEP_FALSE_UPDATES = OFF, to store REAL mutations only of rows in the history table.

if my assumption is correct, this feature is built on top of OUTPUT clause and historical tables loads have logic similar to:

INSERT Table1_History
SELECT * FROM DELETED

Therefore, the easiest way to implement “KEEP_FALSE_UPDATES = OFF” is by using following kind of construction:

INSERT Table1_History
SELECT DELETED.*
EXCEPT
SELECT INSERTED.*

When it come to overhead of the proposed functionality, I believe,  it is an unavoidable evil, which required to be properly documented. However, the same situation is with DATA_CONSISTENCY_CHECK directive, it also require overhead, but that one is definitely useful.

Next to this blog post, I created an Connect item where described the issue and proposal. Dear reader, please vote it up if you think that it can be useful in your usage scenarios as well.

Also, discussion started with Borko Novakovic, a Program Manager in the SQL Server team. However, according to him, requested functionality, likely, will not be included into the current release.

Conclusions

Temporal Tables is a new feature which require some time for discovery, proofing and adoption, it works straightforward and efficient. But, this is first release, and it holds some limitations and peculiarities, like sequential read of tables involved into system versioning or unnecessary versions in the history tables. Nevertheless, we are testing CTP 2.1 and these issues worth to re-check in a RTM release.