Computed columns with temporal tables

Question

My understanding is that it's not possible to set up a temporal table where the base table has a computed column based on a UDF. However, i have seen it suggested that this should be possible if you follow a particular process: Computed Columns In Azure SQL Server 2016 Temporal Tables

The suggested steps are

First turn off system versioning
Then add your computed column to the base table
Verify the "type" of the resulting computed column
Add the column with the appropriate static type to the history table
Turn system versioning back on

It could be that this solution only works in Azure. I've tried it out with SQL Standard using a table that has a UDF computed column of type datetime but without success. Has anyone been able to get this to work and if so could you please share an simple TSQL example.

Tnx

score 1 · Answer 1 · answered Mar 15 '18 at 02:07

It is doable, but UDF has to be schemabinding.

There is an example:

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

CREATE TABLE [dbo].[VitalyTest](
     [ID] [bigint] IDENTITY(1,1) NOT NULL 
    ,[Name] NVARCHAR(255) NOT NULL
    ,[Value] INT NULL
    ,[ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START HIDDEN 
    ,[ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END HIDDEN

    ,[WhenCreated] AS dbo.VitalyUTCtoDate(ValidFrom)

    ,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

Computed columns with temporal tables

1 Answers1