SQL Server: Temporal tables and adding computed columns at runtime

Question

Basically, I want to create computed columns at runtime in temporal tables, for this I had to follow those steps:

1. Turn System_Versioning to off
2. Alter tempral and add the computed column
3. Turn System Versioning to on

Here's an example: I have a temporal table 'FTWV' with a history table 'FTWVHistory', the content of the computed columns will be extracted from a json column 'Data'

  ALTER TABLE [dbo].[FTWV] SET (SYSTEM_VERSIONING = OFF);
  ALTER TABLE [dbo].[FTWV] ADD [Identity] AS JSON_VALUE([Data], '$.Identity'); 
  ALTER TABLE [dbo].[FTWV] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[FTWVHistory]));

When I execute the above queries I get the following error message :

Setting SYSTEM_VERSIONING to ON failed because table 'DatabaseName.dbo.FTWV' has 5 columns and table 'DatabaseName.dbo.FTWVHistory' has 4 columns.

So, I guessed if I Just add the same computed column to the 'FTWVHisotry' table it would work

ALTER TABLE [dbo].[FTWV] SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE [dbo].[FTWV] ADD [Identity] AS JSON_VALUE([Data], '$.Identity'); 
ALTER TABLE [dbo].[FTWVHISTORY] ADD [Identity] AS JSON_VALUE([Data], '$.Identity'); 
ALTER TABLE [dbo].[FTWV] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[FTWVHistory]));

But I get another when executing the last line (setting system_versioning to on) :

Setting SYSTEM_VERSIONING to ON failed because history table 'DatabaseName.dbo.FTWVHistory' has computed column specification. Consider dropping all computed column specifications and trying again.

Answer 1

You can't add a computed column to a temporal table. Instead you need to turn off system versioning and then add the computed column to your system versioned table and then a column with the same data type to your history table.

ALTER TABLE [dbo].[FTWV] SET (SYSTEM_VERSIONING = OFF);
GO
ALTER TABLE [dbo].[FTWV] ADD [Identity] AS JSON_VALUE([Data], '$.Identity'); 
ALTER TABLE [dbo].[FTWVHISTORY] ADD [Identity] nvarchar(4000);
GO
ALTER TABLE [dbo].[FTWV] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[FTWVHistory]));

As a side note, as JSON_VALUE returns an nvarchar(4000) I would suggest explicitly CASTing/CONVERTing your computed column to the appropriate data type, and then creating the column (in your history table) with that data type.