5

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?

| improve this question | | | | |
  • 3
    There might be some answers here but to me, logically, it could make sense that a row that was valid for exactly 0 microseconds "never existed"? In reality, there was no point in time where the values in that row were valid. – Aaron Bertrand Mar 9 '17 at 14:41
6

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.

| improve this answer | | | | |
  • 1
    Thanks, I overlooked that note somehow! I get the same behaviour with second precision (datetime2(0)) instead of microsecond, so it seems unlikely to me that a developer didn’t account for a computer being fast enough to insert & remove a record at the same microsecond. – James L Mar 10 '17 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.