SQL Server Temporal Tables – Should ‘FOR system_time ALL’ Include Equal Start and End Times?

sql serversql-server-2016temporal-tables

Querying a system-versioned temporal table with FOR system_time ALL appears to omit rows where the start and end times are equal.

For example:

/* Create a simple system-versioned table */
CREATE TABLE [dbo].[svt_test](
    [string] [VARCHAR](30) NOT NULL,
    [validFrom] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [validTo]   [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_svt_test] PRIMARY KEY CLUSTERED 
(
    [string] ASC
),
    PERIOD FOR SYSTEM_TIME ([validFrom], [validTo])
) 
WITH
(
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[svt_test_history] )
);

/* 
Insert and immediately delete a row. 
May have to try multiple times to get a row with the same start and end    
timestamp. 
*/
INSERT INTO dbo.svt_test (string) VALUES ('string'); 
DELETE dbo.svt_test WHERE string = 'string';

/* 0 rows */
SELECT * FROM dbo.svt_test;
/* 1 row */
SELECT * FROM dbo.svt_test_history;
/* 
0 rows. 
Should be the "union of rows that belong to the current and the history table" 
  per https://msdn.microsoft.com/en-us/library/dn935015.aspx 
*/
SELECT * FROM dbo.svt_test FOR system_time ALL;

/* 
   CONTAINED IN does not find the row either, even though MSDN says the    
   interval is defined as SysStartTime >= start_date_time AND SysEndTime <=   
   end_date_time
*/
DECLARE @Start DATETIME2(7);
SELECT @Start = validFrom FROM [dbo].[svt_test_history] WHERE string = 'string';
DECLARE @End DATETIME2(7);
SELECT @End = validTo FROM [dbo].[svt_test_history] WHERE string = 'string';
SELECT * FROM dbo.svt_test FOR system_time CONTAINED IN (@Start, @End);

/* Clean up */
ALTER TABLE dbo.svt_test SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.svt_test;
DROP TABLE dbo.svt_test_history;

I've verified this on 2016 SP1 CU1 (12.0.4416) and 2016 CU3 (13.0.2186). I got the same result with datetime2(0) instead of datetime2(7).

Would anyone happen to know whether this is likely a bug or by design? Alternatively, perhaps I've misunderstood something?

Best Answer

I was not able to reproduce what you see. Probably because I have a slow computer. But on the other hand it seems that the SQL Server developers have built this with my slow computer in mind and not your fast one.

If I wrapped the insert and delete in a transaction they got the same start and end timestamp every time.

From Temporal Tables

FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime). Those rows will be generated if you perform multiple updates on the same primary key within the same transaction. In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions. If you need to include those rows in the analysis, query the history table directly.

So, if start and end timestamp are the same they assume it happened in the same transaction.

Related Question