Should 'FOR system_time ALL' include rows where the start and end times are equal?

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 som

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.

StackExchange Database Administrators Q#166685, answer score: 9