ROW START and ROW END in SQL Server temporal tables

Question

I've been using SQL Server system-versioned temporal tables for a while now, and they seem to work really well. I need to write a query with a self-join to the subsequent version of the record, and I'm not sure I'm doing it right.

I need to find cases where a value changed from one value to another, say from "A" to "B" specifically.

Here's my query

SELECT t1.Id,
       t1.MyValue OldValue,  
       c2.MyValue NewValue,  
       t2.ValidFrom DateChanged  
FROM MyTable FOR SYSTEM_TIME ALL t1  
JOIN MyTable FOR SYSTEM_TIME ALL t2 ON t2.Id = t1.Id AND t2.ValidFrom = t1.ValidTo  
WHERE t1.MyValue = 'A'  
AND t2.MyValue = 'B';

"Id" is the primary key and "ValidFrom" and "ValidTo" are the ROW START and ROW END columns.

This seems to work, but I'm wondering about the reliability of that join t2.ValidFrom = t1.ValidTo

• Is it always the case that the subsequent record in the history has ValidFrom exactly equal to ValidTo of the previous record? Are there ever gaps in the sequence?
• Is there any possibility of two records in the history table having the same ValidFrom date?

Answer 1

Is it always the case that the subsequent record in the history has ValidFrom exactly equal to ValidTo of the previous record? Are there ever gaps in the sequence?

That depends on what you mean by "the previous record". If you only ever do UPDATE rather than DELETE and INSERT then yes that will be the case.

But you could delete and reinsert the data, in which case it won't line up. Whether that's what you want to know about is your decision.

Is there any possibility of two records in the history table having the same ValidFrom date?

If you make two updates on the same row within the same transaction then this could happen. The ValidFrom is calculated only once at the beginning of the transaction for all rows.

Using FOR SYSTEM_TIME ALL, no it's not possible, because it uses a filter ValidFrom <> ValidTo automatically, thereby excluding such rows. The issue only appears if you query the history table directly.

---

A safer and more efficient option to get the next value is to just use LAG or LEAD. Do note that in the case of duplicate ValidFrom you will get non-deterministic results (if you query the history table directly), but it will still be a single row.

SELECT
  t.Id,
  t.OldMyValue AS OldValue,  
  t.MyValue AS NewValue,  
  t.ValidFrom AS DateChanged  
FROM (
    SELECT *,
      LAG(t.MyValue) OVER (PARTITION BY t.Id ORDER BY t.ValidFrom) AS OldMyValue
    FROM MyTable FOR SYSTEM_TIME ALL t
) t
WHERE t.MyValue = 'B'
  AND t.OldMyValue = 'A';