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.
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:
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:
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
SELECT * FROM [dbo].[TestUpdates] FOR SYSTEM_TIME FROM '20000101' TO '99991231'
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.
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.
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.