Temporal Tables, and how to deal with computed columns

Black and white Texas Instruments calculator

Long time readers will know I’m a big fan of Temporal Tables since their introduction in SQL Server 2016.

Thanks to my friend Erik Darling (blog | Twitter), I can share a tip when dealing with computed columns.

As I say in my Back to the future with Temporal Tables session, there are several limitations we should be aware of with this feature. One I had not previously considered is that computed columns are not permitted in a history table. This is a problem if we are converting an existing auditing process to make use of temporal tables, and we have computed columns in our table.

Computed columns are columns that are created from an expression based on one or more existing columns in a table. They are useful if we need to index a portion of a wider column, for example.

Erik has come up with a workaround which isn’t that intuitive, but makes sense given the limitation:

It was sort of counter intuitive to have to add the computed column to the regular table, then add a matching non-computed column to the history table, then re-enable system versioning.

Let’s say our current table contains computed columns, and we want to enable system versioning on this table. Whether we have a pre-existing history table or need to create one from scratch, the process is the same. Our history table must exist, with a schema that matches the current table. The main difference is that in place of our computed columns, the column data types in the history table must match the expression of each computed column in the current table.

Using the same example from previous posts, we have a table called [dbo].[Account]. In this case, we have a computed column called AccountCode which takes the first four digits of the AccountNumber column.

The history table’s structure must match the current table, like so:

Note that the computed column is a regular four-character wide string in the history table, CHAR(4).

We enable system versioning using the same method as before:

Hopefully this will help. Thanks Erik!

Leave your thoughts in the comments below.

Photo by Ray Reyes on Unsplash.

4 thoughts on “Temporal Tables, and how to deal with computed columns

  • Hi, this solution works for UDFs that just set a default value, but trying to do the same thing with a function that (for example) returns something from another table fails with the error “Computed column is defined with a user-defined function which is not allowed with system-versioned table ‘VitalyTest’ because it performs user or system data access, or is assumed to perform this access. A function is assumed by default to perform data access if it is not schemabound.”
    I can’t seem to find a solution to this problem anywhere. An example is below:
    IF OBJECT_ID(‘dbo.[VitalyTest]’) IS NOT NULL
    ALTER TABLE [dbo].[VitalyTest]
    SET (
    SYSTEM_VERSIONING = OFF
    );
    GO
    DROP FUNCTION IF EXISTS [Test1AmountSum]
    DROP TABLE IF EXISTS [Test1]
    DROP TABLE IF EXISTS [VitalyTestHistory]
    DROP TABLE IF EXISTS [VitalyTest]
    DROP FUNCTION IF EXISTS [VitalyUTCtoDate]
    GO

    CREATE TABLE [dbo].[Test1]([Amount] MONEY)
    INSERT [Test1] VALUES(1000)
    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
    CREATE FUNCTION [dbo].[Test1AmountSum] ()
    RETURNS MONEY WITH SCHEMABINDING AS
    BEGIN
    DECLARE @res MONEY = 0
    SELECT @res = ISNULL(SUM(Amount),0) FROM [dbo].[Test1]
    RETURN @res
    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)
    ,[SumWhenCreated] AS dbo.Test1AmountSum()
    ,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 [VitalyTest] VALUES('Hello', 1)
    SELECT * FROM [VitalyTest]
    INSERT [Test1] VALUES(3000)
    INSERT [VitalyTest] VALUES('Hello', 2)
    UPDATE [VitalyTest] SET [Name] = 'Goodbye' WHERE [ID] = 1
    SELECT * FROM [VitalyTest]
    SELECT * FROM [VitalyTestHistory]

    Do you have any ideas about what to do in this case?

    • Hi Paweł, my answer is nope, I have no idea what to do in this case. It makes sense from a number of perspectives why this is disallowed, and I’m not aware of any workarounds.

  • Hi all,

    I have a question – MSSQL 2017/2019:
    In base table is ComputedColumn …the definicition is ‘TableName’ + CASt(ID as varchar(255))…. ‘TableName_10201’
    In the temporal table is column with the same name but with definition varchar(261) or 266….
    (its clear the computed column can not be in temporal table) BUT the lenght is strange
    I do not know, how the the length of the column is computed….in other temporal tables the length is different.
    What would be hapened when the definition of computed column has been changed ?….Have To I change the length of column in temporal table manualy or is there any automatic mechanism?

    Thanks for your answer
    David

    • Hi David. I think this is a great opportunity for you to write a simple test to see if the column length in the history table has to match in order for it to work. I look forward to reading your blog post.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: