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
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.
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]
SYSTEM_VERSIONING = OFF
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]
CREATE TABLE [dbo].[Test1]([Amount] MONEY)
INSERT [Test1] VALUES(1000)
CREATE FUNCTION [dbo].[VitalyUTCtoDate] (@UTCDate DATETIME2)
RETURNS DATETIME2 WITH SCHEMABINDING AS
RETURN CASE WHEN @UTCDate < '9999-01-01' THEN DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), @UTCDate) ELSE NULL END;
CREATE FUNCTION [dbo].[Test1AmountSum] ()
RETURNS MONEY WITH SCHEMABINDING AS
DECLARE @res MONEY = 0
SELECT @res = ISNULL(SUM(Amount),0) FROM [dbo].[Test1]
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]))
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.
The simple answer is that you cannot use a function for a computed column unless that function only uses columns in the *same table*. It has nothing to do with being a temporal table, and the limitation is obvious if you think about it – if a field value in the other table gets changed, there is no way for the computed column to know that it must execute the UDF again to update the computed value.
I would suggest making an indexed (materialised) view, which is great because it gets updated when any of it’s dependent tables are updated, although indexed views do have some severe limitations.
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
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.
An easy way to find our the exact type begin returned by the function you have there, is using the built in procedure sp_describe_first_result_set. E.g.