如何向 SQL Server 中的大表添加 NOT NULL 列？

要将 NOT NULL 列添加到包含许多记录的表中，需要应用 DEFAULT 约束。如果表非常大，此约束会导致整个 ALTER TABLE 命令需要很长时间才能运行。这是因为：

假设：

1. DEFAULT 约束修改现有记录。这意味着数据库需要增加每个记录的大小，这会导致它将完整数据页上的记录转移到其他数据页，并且这需要时间。
2. DEFAULT 更新作为原子事务执行。这意味着事务日志需要增长，以便在必要时可以执行回滚。
3. 事务日志跟踪整个记录。因此，即使只修改单个字段，日志所需的空间也将基于整个记录的大小乘以现有记录的数量。这意味着，即使两个表的记录总数相同，向包含小记录的表添加列也会比向包含大记录的表添加列更快。

可能的解决方案：

1. 吸起它并等待该过程完成。只要确保将超时时间设置得很长即可。这样做的问题是，根据记录的数量，可能需要几个小时或几天的时间才能完成。
2. 添加列但允许 NULL。然后，运行 UPDATE 查询来设置现有行的 DEFAULT 值。不要执行更新*。一次更新一批记录，否则您最终会遇到与解决方案 #1 相同的问题。这种方法的问题在于，当您知道这是一个不必要的选项时，您最终会得到一个允许 NULL 的列。我相信有一些最佳实践文档指出，除非有必要，否则不应拥有允许 NULL 的列。
3. 创建具有相同架构的新表。将列添加到该架构中。将原始表中的数据转移过来。删除原始表并重命名新表。我不确定这比#1 更好。

问题：

1. 我的假设正确吗？
2. 这些是我唯一的解决方案吗？如果是的话，哪一个最好？如果没有，我还能做什么？

我的工作也遇到了这个问题。我的解决方案是#2。

这是我的步骤（我使用的是 SQL Server 2005）：

1）将列添加到表中并使用默认值：

ALTER TABLE MyTable ADD MyColumn varchar(40) DEFAULT('')

2）添加一个NOT NULL约束与NOCHECK选项。这NOCHECK不强制执行现有值：

ALTER TABLE MyTable WITH NOCHECK
ADD CONSTRAINT MyColumn_NOTNULL CHECK (MyColumn IS NOT NULL)

3）增量更新表中的值：

GO
UPDATE TOP(3000) MyTable SET MyColumn = '' WHERE MyColumn IS NULL
GO 1000

• update 语句最多只会更新 3000 条记录。这允许当时保存一大块数据。我必须使用“MyColumn IS NULL”，因为我的表没有序列主键。

• GO 1000将执行前面的语句 1000 次。这将更新 300 万条记录，如果您需要更多记录，只需增加此数字即可。它将继续执行，直到 SQL Server 返回 UPDATE 语句的 0 条记录。