sql - 检查外键约束 "online"

Question

如果我们有一个巨大的事实表并想要添加一个新维度，我们可以这样做:

BEGIN TRANSACTION

ALTER TABLE [GiantFactTable]
ADD NewDimValueId INT NOT NULL
CONSTRAINT [temp_DF_NewDimValueId] DEFAULT (-1)
WITH VALUES -- table is not actually rebuilt!

ALTER TABLE [GiantFactTable]
WITH NOCHECK
ADD CONSTRAINT [FK_GiantFactTable_NewDimValue]
FOREIGN KEY ([NewDimValueId])
REFERENCES [NewDimValue] ([Id])

-- drop the default constraint, new INSERTs will specify a value for NewDimValueId column
ALTER TABLE [GiantFactTable]
DROP CONSTRAINT [temp_DF_NewDimValueId]

COMMIT TRANSACTION

注意:以上所有内容仅操作表元数据，并且无论表大小如何都应该很快。然后我们可以运行一个作业来在小事务中回填GiantFactTable.NewDimValueId，这样就不会违反FK。 (此时，任何插入/更新 - 例如回填操作 - 都会由 FK 进行验证，因为它已启用，但不是“受信任的”)

回填后我们知道数据是一致的，我的问题是SQL引擎如何也变得开明？ 无需使表离线。

此命令将使 FK 受信任，但需要架构修改 (Sch-M) 锁定，并且可能需要数小时(数天？)使表脱机:

ALTER TABLE [GiantFactTable]
WITH CHECK CHECK CONSTRAINT [FK_GiantFactTable_NewDimValue]

关于工作负载:表有几百个分区(固定数量)，数据一次追加到一个分区(以循环方式)，从不删除。还有一种持续的读取工作负载，它使用集群键一次从一个分区获取一系列(相对较小的)行。一次检查一个分区并将其脱机是可以接受的。但我找不到任何语法来执行此操作。还有其他想法吗？

Answer 1

我想到了一些想法，但它们并不漂亮:

重定向工作负载并离线运行检查约束

1. 创建一个具有相同结构的新表。
2. 更改“插入”工作负载以插入到新表中
3. 将数据从“读取”工作负载使用的分区复制到新表(或具有相同结构的第三个表)
4. 更改“读取”工作负载以使用新表
5. 运行alter table来检查约束并让它根据需要运行
6. 将两个工作负载更改回主表。
7. 将新行插入主表
8. 删除新表

上述的一个变体是在步骤 3 中将相关分区切换到新表。这应该比复制数据更快，但我认为您必须在约束之后将数据复制(而不仅仅是切换)回来已检查。

将所有数据插入新表

1. 创建一个具有相同结构并启用约束的新表
2. 将“插入”工作负载更改为新表
3. 将旧表中的所有数据批量复制到新表中并等待完成
4. 将“读取”工作负载更改为新表。如果步骤 3 花费的时间太长，并且“读取”工作负载需要仅插入到新表中的行，则您必须手动管理此转换。
5. 删除旧表

使用索引来加速约束检查？

我不知道这是否有效，但您可以尝试在外键列上创建非聚集索引。还要确保外键引用的表上的相关唯一键有索引。 alter table 命令也许能够使用它们来加速检查(至少与全表扫描相比，可以最大限度地减少 IO)。当然，可以在线创建索引以避免任何中断。