ValidFrom can be used in UDF, but ValidTo - not? (computed columns in temporal tables)

Question

Please advise - what am I missing here? Temporal tables ValidTo field cannot be used as a parameter in UDF in computed column, whereas ValidFrom does work with no issues.

SQL Server 2016.

There is an example. Record "Masha" in this case has to have WhenDeleted flag shown as a date, not null:

GO
DROP FUNCTION IF EXISTS [dbo].[VitalyUTCtoDate];
GO
CREATE FUNCTION [dbo].[VitalyUTCtoDate] (@UTCDate DATETIME2)
RETURNS DATETIME2 WITH SCHEMABINDING AS  
BEGIN 
    RETURN CASE WHEN @UTCDate < '9999-01-01' THEN DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), @UTCDate) ELSE NULL END;
END
GO

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'VitalyTest')
BEGIN
    EXEC('ALTER TABLE [dbo].[VitalyTest] SET (SYSTEM_VERSIONING = OFF)');
    EXEC('DROP TABLE IF EXISTS [dbo].[VitalyTest]');
    EXEC('DROP TABLE IF EXISTS [dbo].[VitalyTestHistory]');
END
GO
CREATE TABLE [dbo].[VitalyTest](
     [ID] [bigint] IDENTITY(1,1) NOT NULL 
    ,[Name] NVARCHAR(255) NOT NULL
    ,[Value] INT NULL
    ,[ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN 
    ,[ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    ,[WhenCreated] AS dbo.VitalyUTCtoDate(ValidFrom)
    ,[WhenDeleted] AS dbo.VitalyUTCtoDate(ValidTo)
    ,CONSTRAINT [PK_VitalyTest] PRIMARY KEY CLUSTERED ([ID] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ,PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[VitalyTestHistory]))
GO

-- Insert Data
INSERT INTO dbo.VitalyTest(Name,Value)VALUES('Vasya',234),('Masha',756);

-- Delete Data
DELETE FROM dbo.VitalyTest WHERE Name = 'Masha';

-- Check resutls
SELECT v.*,v.ValidFrom,v.ValidTo
FROM dbo.VitalyTest FOR SYSTEM_TIME ALL AS v
;

-- Clean-up
GO
IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'VitalyTest')
BEGIN
    EXEC('ALTER TABLE [dbo].[VitalyTest] SET (SYSTEM_VERSIONING = OFF)');
    EXEC('DROP TABLE IF EXISTS [dbo].[VitalyTest]');
    EXEC('DROP TABLE IF EXISTS [dbo].[VitalyTestHistory]');
END
GO
DROP FUNCTION IF EXISTS [dbo].[VitalyUTCtoDate];
GO

Answer 1

I found why it happens:

• Temporal table stores state of the data before action happens.
• Temporal table history table doesn't have any computed columns even if main table does.
• Temporal table stores computed column as values (computed values)
• When record is about to be deleted WhenDeleted flag is still NULL at this stage and this NULL is being recorded into History Table before the action
• Then record in main table gets deleted, but this WhenDeleted compute never actually happens and result of this compute never gets stored anywhere