Updating ValidFrom and ValidTo fields using Temporal Tables results in weird behaviour

Question

I'm trying to tell a history table that a certain record is "relevant" in a different time span than its original ValidFrom and ValidTo values, like so:

ALTER TABLE dbo.statusForStudents SET (SYSTEM_VERSIONING = OFF);  

update history.statusForStudents set validfrom='2020-10-25 11:12:07.40',ValidTo='2020-10-26 12:48:31.19' where ValidFrom='2020-12-03 11:12:07.40' and id=1066255

ALTER TABLE statusForStudents
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE =history.statusForStudents)); 

That is:
I turn off TT,
update the relevant record,
and turn on TT again.

And this actually works, However, when I update the current value, after TT in on again:

update statusForStudents set PrimaryStatusID=1 where id=1066255  

It seems that the TT mechanism, for some reason "skips" a few seconds between the previous ValidTo time, and the next ValidFrom:

I've never seen a gap like that in history tables..

Is this a bug?
Is there a way to overcome it?

Answer 1

Explanation

This would make sense if the history row that you updated was valid from 2020-12-03 11:12:07.40 to 2020-12-03 12:12.67. The start time does match with your history update statement...

Reproduction

Create temporal table

create table StatusForStudents
(
    Id bigint NOT NULL PRIMARY KEY CLUSTERED
  , PrimaryStatusId int
  , SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.StatusForStudentsHistory));

Create some history to play with

-- status = 1 for 1 sec
insert into StatusForStudents (Id, PrimaryStatusId) values (1066255, 1);
WAITFOR DELAY '00:00:01';

-- status = 100 for 2 sec
update StatusForStudents set PrimaryStatusId = 100 where Id = 1066255;
WAITFOR DELAY '00:00:02';

-- status = 3 as current status
update StatusForStudents set PrimaryStatusId = 3 where Id = 1066255;

This gives me:

-- StatusForStudents
Id      PrimaryStatusId SysStartTime                SysEndTime
------- --------------- --------------------------- ---------------------------
1066255               3 2020-12-06 19:55:40.9777475 9999-12-31 23:59:59.9999999

--StatusForStudentsHistory
Id      PrimaryStatusId SysStartTime                SysEndTime
------- --------------- --------------------------- ---------------------------
1066255               1 2020-12-06 19:55:37.9464833 2020-12-06 19:55:38.9621205
1066255             100 2020-12-06 19:55:38.9621205 2020-12-06 19:55:40.9777475

Mess with the history

ALTER TABLE dbo.statusForStudents SET (SYSTEM_VERSIONING = OFF);

-- get start date for history row with status = 100
declare @Reference datetime2;
select @Reference = h.SysStartTime
from StatusForStudentsHistory h
where h.Id = 1066255
  and h.PrimaryStatusId = 100;

-- update history row for status = 100 through start date filter
update statusForStudentsHistory
set SysStartTime = '2020-10-25 11:12:07.40',
    SysEndtime = '2020-10-26 12:48:31.19'
where Id = 1066255
  and SysStartTime = @Reference;

ALTER TABLE statusForStudents SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.StatusForStudentsHistory));

New update

-- status = 1 as current status
update StatusForStudents set PrimaryStatusID = 1 where Id = 1066255;

This gives me:

-- StatusForStudents
Id      PrimaryStatusId SysStartTime                SysEndTime
------- --------------- --------------------------- ---------------------------
1066255               1 2020-12-06 19:55:40.9933283 9999-12-31 23:59:59.9999999

--StatusForStudentsHistory
Id      PrimaryStatusId SysStartTime                SysEndTime
------- --------------- --------------------------- ---------------------------
1066255             100 2020-10-25 11:12:07.4000000 2020-10-26 12:48:31.1900000
1066255               1 2020-12-06 19:55:37.9464833 2020-12-06 19:55:38.9621205
1066255               3 2020-12-06 19:55:40.9777475 2020-12-06 19:55:40.9933283

There now appears to be a 2 second gap between the rows with status 1 and 3 in the history table. However, this is exactly the row with a 2 second duration (status = 100) that was updated. In this case the "gap" was introduced by the history update.

Conclusion

Manually updating the history table requires the update of adjacent rows in the history table to avoid gaps and overlaps. Most likely on both the place where the history row is moved from and the place where the row is moved to.

Fiddle to see it in action.