SQL Server 2017

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EXAMPLE_TABLE]') AND type in (N'U'))
ALTER TABLE [dbo].[EXAMPLE_TABLE] SET ( SYSTEM_VERSIONING = OFF  )

DROP TABLE IF EXISTS [dbo].[EXAMPLE_TABLE]

DROP TABLE IF EXISTS [dbo].[EXAMPLE_TABLE_HISTORY]


CREATE TABLE dbo.EXAMPLE_TABLE_HISTORY (
    ID INT NOT NULL,
    -- 'data'
    ColA VARCHAR(255) NULL,
    ColB VARCHAR(255) NULL,
    ColC VARCHAR(255) NULL,
    SysStartTime DATETIME2 NOT NULL,
    SysEndTime DATETIME2 NOT NULL
);
CREATE CLUSTERED INDEX IX_EXAMPLE_TABLE_HISTORY__SysEndTime_SysStartTime
    ON dbo.EXAMPLE_TABLE_HISTORY (SysEndTime ASC, SysStartTime ASC);

CREATE TABLE dbo.EXAMPLE_TABLE (
    ID INT NOT NULL IDENTITY(104729, 1),
    CONSTRAINT PK_EXAMPLE_TABLE
        PRIMARY KEY CLUSTERED (ID),
    -- 'data'
    ColA VARCHAR(255) NULL,
    ColB VARCHAR(255) NULL,
    ColC VARCHAR(255) NULL,
    -- system versioning
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EXAMPLE_TABLE_HISTORY));


-- perform simple dml statements
DECLARE @t0 DATETIME2 = SYSUTCDATETIME();
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', NULL, NULL);

DECLARE @t1 DATETIME2 = SYSUTCDATETIME();
UPDATE dbo.EXAMPLE_TABLE SET ColB = 'b' WHERE ColA = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
INSERT INTO dbo.EXAMPLE_TABLE (ColA, ColB, ColC) VALUES ('d', NULL, 'f');

DECLARE @t2 DATETIME2 = SYSUTCDATETIME();
DELETE dbo.EXAMPLE_TABLE WHERE ColA = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

DECLARE @t3 DATETIME2 = SYSUTCDATETIME();

-- inspect tables at specific points in time
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t0;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t1;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t2;
SELECT * FROM dbo.EXAMPLE_TABLE FOR SYSTEM_TIME AS OF @t3;
SELECT * FROM dbo.EXAMPLE_TABLE;

SELECT *, CAST(sysstarttime AS VARCHAR(50)) AS [FullSysStartTime], CAST(SysEndTime AS VARCHAR(50)) AS [FullSysEndTime] 
FROM dbo.EXAMPLE_TABLE_HISTORY;

SELECT CAST(@t0 AS varchar(50)) AS [@t0]; 


ID
ColA
ColB
ColC


104730
d
null
f




ID
ColA
ColB
ColC


104730
d
null
f




ID
ColA
ColB
ColC


104730
d
null
f




ID
ColA
ColB
ColC


104730
d
null
f




ID
ColA
ColB
ColC


104730
d
null
f




ID
ColA
ColB
ColC
SysStartTime
SysEndTime
FullSysStartTime
FullSysEndTime


104729
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
null
null
22/10/2019 01:08:00
22/10/2019 01:08:00
2019-10-22 01:08:00.3593757
2019-10-22 01:08:00.3593757

104729
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
b
null
22/10/2019 01:08:00
22/10/2019 01:08:00
2019-10-22 01:08:00.3593757
2019-10-22 01:08:00.3593757




@t0


2019-10-22 01:08:00.3593757


select @@version


(No column name)


Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64) 
	Jun 15 2019 00:45:05 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



select @@options


(No column name)


5496

ID	ColA	ColB	ColC	SysStartTime	SysEndTime	FullSysStartTime	FullSysEndTime
104729	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	null	null	22/10/2019 01:08:00	22/10/2019 01:08:00	2019-10-22 01:08:00.3593757	2019-10-22 01:08:00.3593757
104729	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	b	null	22/10/2019 01:08:00	22/10/2019 01:08:00	2019-10-22 01:08:00.3593757	2019-10-22 01:08:00.3593757