add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- 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;


-- check result

select * from StatusForStudents;

select * from StatusForStudentsHistory;

Id PrimaryStatusId SysStartTime SysEndTime
1066255 3 2024-08-02 10:04:42.8011328 9999-12-31 23:59:59.9999999
Id PrimaryStatusId SysStartTime SysEndTime
1066255 1 2024-08-02 10:04:39.7759889 2024-08-02 10:04:40.7855223
1066255 100 2024-08-02 10:04:40.7855223 2024-08-02 10:04:42.8011328
-- start messing 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;

1 rows affected
-- stop messing with the history

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;

-- check result

select * from StatusForStudents;

select * from StatusForStudentsHistory;
--> the line with status 100 is moved
--> a 2 sec gap was introduced by moving the row with status = 100

Id PrimaryStatusId SysStartTime SysEndTime
1066255 1 2024-08-02 10:04:43.0823177 9999-12-31 23:59:59.9999999
Id PrimaryStatusId SysStartTime SysEndTime
1066255 100 2020-10-25 11:12:07.4000000 2020-10-26 12:48:31.1900000
1066255 1 2024-08-02 10:04:39.7759889 2024-08-02 10:04:40.7855223
1066255 3 2024-08-02 10:04:42.8011328 2024-08-02 10:04:43.0823177