Azure SQL Server 2016临时表中的计算列 [英] Computed Columns In Azure SQL Server 2016 Temporal Tables

问题描述

我正在查看创建时态表 https://msdn.microsoft.com/en-us/library/mt604462.aspx ，但是我无法打开几个具有计算列的表.

I am looking at creating temporal tables https://msdn.microsoft.com/en-us/library/mt604462.aspx in our database but I cant on a couple of tables that have computed columns.

返回的错误消息很容易解释

The error message returned is rather self explanatory

使用用户定义的函数定义了计算列，而系统版本化的表则不允许使用该函数"

"Computed column is defined with a user-defined function which is not allowed with system-versioned table"

但是我希望有一种方法可以排除或忽略被跟踪的列?

but I was hoping there was a way to exclude or ignore columns from being tracked?

我尝试删除创建历史记录表的计算列，然后将计算列添加回表中，但这没有用.

I have tried dropping the computed column creating the history table then adding the computed column back into the table but this didn't work.

感谢您的帮助.

谢谢

编辑-
我无法找到一种方法来忽略被跟踪的列，但是我们能够重构出使用UDF的列，从而使我们能够使用时态表.

Edit -
I wasn't able to find a way to ignore columns from being tracked but we were able to refactor out the columns that used UDFs thus enabling us to use temporal tables.

推荐答案

我正在努力将计算列添加到现有的系统版本化的表中.万一其他有类似问题的人落在这里，我最终意识到历史表不会以相同的方式对待列.最终，它类似于在基表上具有IDENTITY列，但是这将导致历史记录表上出现规则的INT字段.

I was struggling with adding a computed column to an existing system-versioned table. In case anyone else with a similar problem lands here, I finally realized that the history table doesn't treat the column the same way. It ends up being similar to having an IDENTITY column on the base table, but that would result in a regular INT field on the history table.

如果您尝试将计算列添加到系统版本化的(临时)表中:

If you are attempting to add a computed column to a system-versioned (temporal) table:

• 首先关闭系统版本控制
• 然后将您的计算列添加到基表中
• 验证结果计算列的类型"
• 将具有适当静态类型的列添加到历史记录表中
• 重新打开系统版本控制(不要忘记指定历史记录表)

我觉得很奇怪，当重新打开系统版本控制时，您可能会意外地省略history_table.我希望它会恢复对同一表的版本控制，或者考虑到它可能有点意外的行为而引发某种错误.

I find it rather odd that you can accidentally omit the history_table when turning system versioning back on. I'd expect either it would resume versioning to the same table OR throw some kind of error considering it might be a bit unexpected behavior.

@ pasquale-ceglie-我的声誉不足以发表评论，但我想扩大您的发言范围.您应该能够将大多数计算列与时态表一起使用，只需更多地手动即可.基本上，您不能复制带有计算列的架构定义，但是可以在尝试打开所有内容之前复制生成的列并生成适当的历史记录表.这两个表之间的定义略有不同(起初让我很困惑).我订阅了这里，如果上面的内容不清楚并且很好奇，请对我进行ping操作.

@pasquale-ceglie - I don't have enough reputation to comment, but I wanted to expand on what you said. You should be able to use most computed columns with temporal tables, just more manually. Basically you can't copy the schema definition with the computed columns, you can however replicate the resulting columns and generate the appropriate history table before trying to turn everything on. The definitions are just a bit different between the two tables (was quite confusing to me at first). I subscribed here, ping me if the above isn't clear and are curious.

这篇关于Azure SQL Server 2016临时表中的计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！