向大表中添加计算的持久化列

我需要将计算的持久化列添加到一个大表中(~1B行)。我是否可以分批或使用现有的预计算列来完成这项工作?

我首先尝试了简单的列:

ALTER TABLE [dbo].[T] ADD [X] [decimal](32, 6) NULL
GO
UPDATE [dbo].[T]
SET [X] = [Y] / [Z]

因此,我在一个周期内进行了批量更新-所有更新都在7小时内完成,并且没有阻止用户查询。

现在我需要为新记录自动维护这个列—因此考虑持久化计算列。我希望表的停机时间尽可能小(理想情况下没有)。看看简单更新和批处理更新的经验,我想以某种方式分批执行,或者使用现有的列(保存计算结果的列)-有什么方法可以实现这一点吗?

我需要持久化列,因为我需要在以后对它进行索引,而且还因为我正在优化查询,其中计算标量(我要持久化的确切表达式)需要相当长的时间。 我也在考虑索引视图,但我担心长时间运行的事务也会出现同样的问题。

Sql Server 2016(企业版)。简单的恢复模式。

作为我将来的参考(如果有人觉得这很有帮助的话)-以下是我考虑过的(和测试过的)选项:

ALTER TABLE [dbo].[T] ADD [X] AZ [Y] / [Z] PERSISTED

优点:简单,正直

缺点:单个事务-需要大量的事务日志,如果中途失败-所有进程都将丢失;无法联机完成-对表的任何查询都被锁定

• 索引视图

  --索引视图 作为 [Y] 哦, [Y] /(NULLIF(Z,0))为[Z] [dbo].[T] 去吧

  --物化视图的第一个索引必须是唯一的和聚集的 创建唯一聚集索引IDX\u T\u视图
  在[dbo].[T\u view]([Z])

优点:添加列时,基础表不会变得支离破碎

缺点:主要是需要索引的唯一性。再加上一笔交易

• 具有索引的非持久化计算列:

  更改表[dbo].[T]添加[X]AZ[Y]/[Z]

  (

优点:快速,没有表碎片(因为没有执行物理更改)

缺点:每次选择计算列时仍然需要计算它

• 插入/更新触发器:

缺点:我们需要确保完整性(当我们批量更新表时,菜单中可能会有一些插入/更新)

• 创建新表并迭代移动数据:

优点:和扳机一样。计算列通常比触发器性能更好;我们可以计划数据移动的方式,以便我们得到很好的碎片整理表

缺点:与触发器相同。另外我们还需要额外的空间。

移动数据构建索引两天后,我发现计算列(即使是持久的)不能用在筛选索引的where子句中。即使在我将它从filter expression移到include columns之后(这样SQL仍然可以基于这个索引执行选择),性能也会急剧下降。所以我需要转换为插入触发器解决方案。