要向记录很多的表中添加NOT NULL列,需要应用DEFAULT约束。如果表非常大,则整个ALTER TABLE命令需要花费很长时间才能运行。这是因为:
假设:
- DEFAULT约束修改现有记录。这意味着数据库需要增加每个记录的大小,这导致它将完整数据页上的记录移动到其他数据页,从而需要时间。
- DEFAULT更新作为原子事务执行。这意味着如果需要执行回滚操作,事务日志将需要增长。
- 事务日志跟踪整个记录。因此,即使只修改单个字段,日志所需的空间也将基于整个记录的大小乘以现有记录数。这意味着,即使两个表的总记录数相同,向具有小记录的表添加列将比向具有大记录的表添加列更快。
可能的解决方案:
- 耐心等待过程完成。只需确保设置超时时间很长即可。但问题是,这可能需要几个小时或几天,具体取决于记录的数量。
- 添加列但允许NULL。之后,运行UPDATE查询以为现有行设置DEFAULT值。不要执行UPDATE *。 分批处理记录,否则将出现与解决方案#1相同的问题。这种方法的问题是,您最终拥有一个允许NULL的列,而您知道这是一个不必要的选项。我相信有一些最佳实践文档指出,除非必要,否则不应该有允许NULL的列。
- 创建具有相同架构的新表。将列添加到该架构中。将数据从原始表转移到新表中。删除原始表并重命名新表。我不确定这比#1好在哪里。
问题:
- 我的假设正确吗?
- 这些是我唯一的解决方案吗?如果是,哪个是最好的?如果不是,我还能做什么?
回答:
1. 您的假设是正确的。 2. 不是唯一的解决方案。解决方案#2是最好的选择,因为它避免了在表上执行DDL操作,同时又不会在列中包含NULL值。此外,可以考虑更改数据库的架构来减轻这种情况的发生。
WITH NOCHECK
:"如果您不想针对现有数据验证新的CHECK或FOREIGN KEY约束,请使用WITH NOCHECK。除非在极少数情况下,我们不建议这样做。新的约束将在所有后续数据更新中进行评估。当添加约束时,由WITH NOCHECK抑制的任何约束违规可能会导致将来的更新失败,如果它们更新与约束不符合的数据行。" - Kirk Woll