SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Data (
    Id INT NOT NULL CONSTRAINT PK_Data PRIMARY KEY,
    Value VARCHAR(100),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    VALIDTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON)
INSERT INTO Data(Id, Value)
VALUES (1, 'AAA'), (2, 'BBB')

SELECT * FROM Data FOR SYSTEM_TIME ALL ORDER BY ValidFrom, Id


Id
Value
ValidFrom
VALIDTo


1
AAA
2026-06-01 14:27:59.5457420
9999-12-31 23:59:59.9999999

2
BBB
2026-06-01 14:27:59.5457420
9999-12-31 23:59:59.9999999


WAITFOR DELAY '00:00:02'

-- These two updates generate slightly different ValidFrom times 
UPDATE DATA SET Value = 'AAA2' WHERE Id = 1
UPDATE DATA SET Value = 'BBB2' WHERE Id = 2

SELECT * FROM Data FOR SYSTEM_TIME ALL ORDER BY ValidFrom, Id


Id
Value
ValidFrom
VALIDTo


1
AAA
2026-06-01 14:27:59.5457420
2026-06-01 14:28:01.5600547

2
BBB
2026-06-01 14:27:59.5457420
2026-06-01 14:28:01.5641918

1
AAA2
2026-06-01 14:28:01.5600547
9999-12-31 23:59:59.9999999

2
BBB2
2026-06-01 14:28:01.5641918
9999-12-31 23:59:59.9999999


WAITFOR DELAY '00:00:02'

-- With a transaction, the same ValidFrom time is used for all activity
-- covered by the transaction
BEGIN TRANSACTION
UPDATE DATA SET Value = 'AAA3' WHERE Id = 1
UPDATE DATA SET Value = 'BBB3' WHERE Id = 2
COMMIT

SELECT * FROM Data FOR SYSTEM_TIME ALL ORDER BY ValidFrom, Id


Id
Value
ValidFrom
VALIDTo


1
AAA
2026-06-01 14:27:59.5457420
2026-06-01 14:28:01.5600547

2
BBB
2026-06-01 14:27:59.5457420
2026-06-01 14:28:01.5641918

1
AAA2
2026-06-01 14:28:01.5600547
2026-06-01 14:28:03.5747970

2
BBB2
2026-06-01 14:28:01.5641918
2026-06-01 14:28:03.5747970

1
AAA3
2026-06-01 14:28:03.5747970
9999-12-31 23:59:59.9999999

2
BBB3
2026-06-01 14:28:03.5747970
9999-12-31 23:59:59.9999999


WAITFOR DELAY '00:00:02'

-- Update statements will trigger new history, even if actual values have not changed.
UPDATE DATA SET Value = 'AAA3' WHERE Id = 1 -- No actual change
UPDATE DATA SET Value = Value WHERE Id = 2 -- No actual change

SELECT * FROM Data FOR SYSTEM_TIME ALL ORDER BY ValidFrom, Id


Id
Value
ValidFrom
VALIDTo


1
AAA
2026-06-01 14:27:59.5457420
2026-06-01 14:28:01.5600547

2
BBB
2026-06-01 14:27:59.5457420
2026-06-01 14:28:01.5641918

1
AAA2
2026-06-01 14:28:01.5600547
2026-06-01 14:28:03.5747970

2
BBB2
2026-06-01 14:28:01.5641918
2026-06-01 14:28:03.5747970

1
AAA3
2026-06-01 14:28:03.5747970
2026-06-01 14:28:05.5868039

2
BBB3
2026-06-01 14:28:03.5747970
2026-06-01 14:28:05.5909604

1
AAA3
2026-06-01 14:28:05.5868039
9999-12-31 23:59:59.9999999

2
BBB3
2026-06-01 14:28:05.5909604
9999-12-31 23:59:59.9999999

Id	Value	ValidFrom	VALIDTo
1	AAA	2026-06-01 14:27:59.5457420	9999-12-31 23:59:59.9999999
2	BBB	2026-06-01 14:27:59.5457420	9999-12-31 23:59:59.9999999

Id	Value	ValidFrom	VALIDTo
1	AAA	2026-06-01 14:27:59.5457420	2026-06-01 14:28:01.5600547
2	BBB	2026-06-01 14:27:59.5457420	2026-06-01 14:28:01.5641918
1	AAA2	2026-06-01 14:28:01.5600547	9999-12-31 23:59:59.9999999
2	BBB2	2026-06-01 14:28:01.5641918	9999-12-31 23:59:59.9999999