CODEWITHSUNDEEP
t-sqlsql-server-2016temporal-tables
Bugz Codes
Bugz Codes

Reputation: 33

Computed columns with temporal tables

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

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

Upvotes: 0

Views: 2855

Answers (1)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

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

Upvotes: 1

Related Questions