Basically, I want to create computed columns at runtime in temporal tables, for this I had to follow those steps:
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.
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 CAST
ing/CONVERT
ing your computed column to the appropriate data type, and then creating the column (in your history table) with that data type.