Temporal Tables, and how to deal with computed columns

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.

DROP TABLE IF EXISTS [dbo].[Account];
GO

CREATE TABLE [dbo].[Account] (
   [AccountID] INT IDENTITY(1, 1) NOT NULL,
   [AccountNumber] BIGINT NOT NULL,
   [AccountName] NVARCHAR(255) NOT NULL,
   [Address] NVARCHAR(1000) NOT NULL,
   [Telephone] NVARCHAR(20) NOT NULL,
   [AccountCode] AS CAST(LEFT([AccountNumber], 4) AS CHAR(4)),
   [CreateDate] DATETIME2(7) NOT NULL,
   [ModifiedDate] DATETIME2(7) NOT NULL,
   CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ([AccountID] ASC)
);
GO

-- Default constraints for the Create and Modified Dates
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [DF_Account_CreateDate]
DEFAULT(SYSUTCDATETIME()) FOR [CreateDate];
GO

ALTER TABLE [dbo].[Account] ADD CONSTRAINT [DF_Account_ModifiedDate]
DEFAULT(SYSUTCDATETIME()) FOR [ModifiedDate];
GO

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

CREATE TABLE [History].[Account] (
   [AccountID] INT IDENTITY(1, 1) NOT NULL,
   [AccountNumber] BIGINT NOT NULL,
   [AccountName] NVARCHAR(255) NOT NULL,
   [Address] NVARCHAR(1000) NOT NULL,
   [Telephone] NVARCHAR(20) NOT NULL,
   [AccountCode] CHAR(4) NOT NULL,
   [CreateDate] DATETIME2(7) NOT NULL,
   [ModifiedDate] DATETIME2(7) NOT NULL
);
GO

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:

ALTER TABLE [dbo].[Account]
SET (
   SYSTEM_VERSIONING = ON (
      HISTORY_TABLE = [History].[Account]
   )
);
GO

Hopefully this will help. Thanks Erik!

Leave your thoughts in the comments below.

Photo by Ray Reyes on Unsplash.