SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.SomeTable (YourID int IDENTITY CONSTRAINT PK_YourID PRIMARY KEY,
                            SomeColumn varchar(10),
                            ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
                            ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
                            PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SomeTableHistory));
INSERT INTO dbo.SomeTable (SomeColumn)
VALUES('abc');
1 rows affected

UPDATE dbo.SomeTable
SET SomeColumn = 'def'
WHERE YourID = 1
1 rows affected

ALTER TABLE dbo.SomeTable ADD AnotherColumn decimal(10,2) NOT NULL CONSTRAINT DF_AnotherColumn DEFAULT 0;
ALTER TABLE dbo.SomeTable ADD SomeDate date NULL CONSTRAINT DF_SomeDate DEFAULT '20220101' WITH VALUES;
SELECT *
FROM dbo.SomeTable;


YourID
SomeColumn
ValidFrom
ValidTo
AnotherColumn
SomeDate


1
def
2022-07-14 11:10:33.7582139
9999-12-31 23:59:59.9999999
0.00
2022-01-01


SELECT *
FROM dbo.SomeTableHistory;


YourID
SomeColumn
ValidFrom
ValidTo
AnotherColumn
SomeDate


1
abc
2022-07-14 11:10:33.7426028
2022-07-14 11:10:33.7582139
0.00
2022-01-01


ALTER TABLE dbo.SomeTable SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.SomeTable;
DROP TABLE dbo.SomeTableHistory;