英文:
SQL Server: Temporal tables and adding computed columns at runtime
问题
基本上，我想在时间表中在运行时创建计算列，为此我必须按照以下步骤操作：
- 将System_Versioning关闭
- 修改时间列并添加计算列
- 将System Versioning打开
这是一个示例：
我有一个时间表'FTWV'和一个历史表'FTWVHistory'，计算列的内容将从一个json列'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]));
当我执行上述查询时，我收到以下错误消息：
将SYSTEM_VERSIONING设置为ON失败，因为表'DatabaseName.dbo.FTWV'有5列，而表'DatabaseName.dbo.FTWVHistory'有4列。
因此，我猜想如果我只是将相同的计算列添加到'FTWVHisotry'表中，它可能会起作用。
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]));
但是当执行最后一行（设置system_versioning为on）时，我收到另一个错误消息：
将SYSTEM_VERSIONING设置为ON失败，因为历史表'DatabaseName.dbo.FTWVHistory'具有计算列规范。考虑删除所有计算列规范，然后重试。
英文:
Basically, I want to create computed columns at runtime in temporal tables, for this I had to follow those steps:
- Turn System_Versioning to off
- Alter tempral and add the computed column
- 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.
答案1
得分: 1
你不能向临时表添加计算列。相反，您需要关闭系统版本控制，然后将计算列添加到您的系统版本表中，然后在历史表中添加一个具有相同数据类型的列。
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]));
另外，由于JSON_VALUE返回nvarchar(4000)，我建议显式地将您的计算列转换为适当的数据类型，然后在历史表中创建该数据类型的列。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论