1dNcAndW

1GO
2DROP FUNCTION IF EXISTS [dbo].[VitalyUTCtoDate];
3GO
4CREATE FUNCTION [dbo].[VitalyUTCtoDate] (@UTCDate DATETIME2)
5RETURNS DATETIME2 WITH SCHEMABINDING AS  
6BEGIN 
7    RETURN CASE WHEN @UTCDate < '9999-01-01' THEN DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), @UTCDate) ELSE NULL END;
8END
9GO
10
11IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'VitalyTest')
12BEGIN
13    EXEC('ALTER TABLE [dbo].[VitalyTest] SET (SYSTEM_VERSIONING = OFF)');
14    EXEC('DROP TABLE IF EXISTS [dbo].[VitalyTest]');
15    EXEC('DROP TABLE IF EXISTS [dbo].[VitalyTestHistory]');
16END
17GO
18CREATE TABLE [dbo].[VitalyTest](
19     [ID] [bigint] IDENTITY(1,1) NOT NULL 
20    ,[Name] NVARCHAR(255) NOT NULL
21    ,[Value] INT NULL
22    ,[ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN 
23    ,[ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
24    ,[WhenCreated] AS dbo.VitalyUTCtoDate(ValidFrom)
25    ,[WhenDeleted] AS dbo.VitalyUTCtoDate(ValidTo)
26    ,CONSTRAINT [PK_VitalyTest] PRIMARY KEY CLUSTERED ([ID] ASC)
27        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
28    ,PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
29)
30WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[VitalyTestHistory]))
31GO
32
33-- Insert Data
34INSERT INTO dbo.VitalyTest(Name,Value)VALUES('Vasya',234),('Masha',756);
35
36-- Delete Data
37DELETE FROM dbo.VitalyTest WHERE Name = 'Masha';
38
39-- Check resutls
40SELECT v.*,v.ValidFrom,v.ValidTo
41FROM dbo.VitalyTest FOR SYSTEM_TIME ALL AS v
42;
43
44-- Clean-up
45GO
46IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'VitalyTest')
47BEGIN
48    EXEC('ALTER TABLE [dbo].[VitalyTest] SET (SYSTEM_VERSIONING = OFF)');
49    EXEC('DROP TABLE IF EXISTS [dbo].[VitalyTest]');
50    EXEC('DROP TABLE IF EXISTS [dbo].[VitalyTestHistory]');
51END
52GO
53DROP FUNCTION IF EXISTS [dbo].[VitalyUTCtoDate];
54GO