如何在 SQL Server 的大表中添加 NOT NULL 列? [英] How do you add a NOT NULL Column to a large table in SQL Server?

问题描述

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

To add a NOT NULL Column to a table with many records, a DEFAULT constraint needs to be applied. This constraint causes the entire ALTER TABLE command to take a long time to run if the table is very large. This is because:

假设:

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

可能的解决方案:

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

问题:

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

推荐答案

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

I ran into this problem for my work also. And my solution is along #2.

这是我的步骤(我使用的是 SQL Server 2005):

Here are my steps (I am using SQL Server 2005):

1) 将列添加到具有默认值的表中:

1) Add the column to the table with a default value:

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

2) 使用 NOCHECK 选项添加 NOT NULL 约束.NOCHECK 不对现有值强制执行:

2) Add a NOT NULL constraint with the NOCHECK option. The NOCHECK does not enforce on existing values:

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

3) 增量更新表中的值:

3) Update the values incrementally in table:

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

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

  • The update statement will only update maximum 3000 records. This allow to save a chunk of data at the time. I have to use "MyColumn IS NULL" because my table does not have a sequence primary key.

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

    GO 1000 will execute the previous statement 1000 times. This will update 3 million records, if you need more just increase this number. It will continue to execute until SQL Server returns 0 records for the UPDATE statement.

    这篇关于如何在 SQL Server 的大表中添加 NOT NULL 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！