如何在SQL Server中向大表添加非空列？

Question

要向记录很多的表中添加NOT NULL列，需要应用DEFAULT约束。如果表非常大，则整个ALTER TABLE命令需要花费很长时间才能运行。这是因为：

假设：

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

可能的解决方案：

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

问题：

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

回答：

1. 您的假设是正确的。 2. 不是唯一的解决方案。解决方案＃2是最好的选择，因为它避免了在表上执行DDL操作，同时又不会在列中包含NULL值。此外，可以考虑更改数据库的架构来减轻这种情况的发生。

- MrB

Answer 1

我在工作中也遇到了这个问题，我的解决方案与第二种方法相似。

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

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

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

2) 使用NOCHECK选项添加一个NOT NULL约束。 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

• 更新语句最多只能更新3000条记录。这样可以一次性保存一大块数据。我必须使用"MyColumn IS NULL"，因为我的表没有序列主键。

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

- DHornpout

---

我原本不知道 "GO" 后面可以加上数字，你改变了我的世界。 - Eddie Deyo

值得一提的是，MSDN强烈反对使用WITH NOCHECK："如果您不想针对现有数据验证新的CHECK或FOREIGN KEY约束，请使用WITH NOCHECK。除非在极少数情况下，我们不建议这样做。新的约束将在所有后续数据更新中进行评估。当添加约束时，由WITH NOCHECK抑制的任何约束违规可能会导致将来的更新失败，如果它们更新与约束不符合的数据行。" - Kirk Woll

此外：“查询优化器不考虑使用 WITH NOCHECK 定义的约束。这些约束将被忽略，直到使用 ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL 重新启用它们。” - Kirk Woll

把以下内容追加到答案底部标记约束已检查的部分可能是值得的：ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyColumn_NOTNULL - http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx。 - Matthew Steeples

Martin Smith展示了为什么在这里使用NOCHECK是一个问题。 - Aaron Bertrand

Answer 2

这是我会尝试的方法：

• 完整备份数据库。
• 添加新列，允许为空 - 不设置默认值。
• 设置简单恢复模式，每个批次提交后立即截断事务日志。
• SQL 语句为: ALTER DATABASE XXX SET RECOVERY SIMPLE
• 按照之前讨论的分批运行更新操作，并在每次提交后执行。
• 重置新列以不再允许空值。
• 回到正常的完整恢复模式。
• SQL 语句为: ALTER DATABASE XXX SET RECOVERY FULL
• 再次备份数据库。

使用简单恢复模式并不能停止日志记录，但显著减少其影响。这是因为服务器在每次提交后丢弃恢复信息。

- RoadWarrior

---

根据表的大小，确保您有足够的日志空间。将列重置为不再允许为空会消耗大量日志。 - Lamar

Answer 3

更新最新信息。

在SQL Server 2012中，以下情况下可以进行在线操作：

1. 仅适用于企业版
2. 默认值必须是运行时常量

对于第二个要求，例如字面常量或函数（例如GETDATE（））评估为所有行相同的值。默认值为NEWID（）不符合要求，仍然会立即更新所有行。

对于符合条件的默认值，SQL Server将对其进行评估，并将结果存储为列元数据中的默认值，因此这与创建的默认约束无关（如果不再需要，甚至可以删除）。可以在sys.system_internals_partition_columns中查看此信息。该值直到下一次更新才会写入行。

有关更多详细信息，请单击此处。

- Martin Smith

Answer 4

你可以：

1. 开始一个事务。
2. 在原始表上获取写锁，以防其他人写入它。
3. 创建具有新模式的影子表。
4. 将所有数据从原始表中转移过来。
5. 执行sp_rename来重命名旧表。
6. 执行sp_rename来重命名新表。
7. 最后，你提交事务。

这种方法的优点是，在长时间的处理过程中，读者仍然能够访问该表，并且你可以在后台执行任何类型的模式更改。

- sambo99

Answer 5

承认这是一个老问题。我的同事最近告诉我，他能够在一个有1360万行的表上使用单个alter table语句完成操作。在SQL Server 2012中，它在一秒钟内完成。我能够在一个有800万行的表上进行确认。后来版本的SQL Server发生了什么变化？

Alter table mytable add mycolumn char(1) not null default('N');

- Kenneth Xu

---

（几年后）在2012年开始的企业SQL版本中，添加一个带有默认值的NOT NULL列是几乎瞬间完成的在线操作。来源：https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017#adding-not-null-columns-as-an-online-operation - Null Reference

Answer 6

我认为这取决于你使用的SQL版本，但如果你选择了第二个选项，最后通过alter table table设置默认值来使其非空，会怎样呢？

它会很快吗，因为它看到所有的值都不为空吗？

- Pyrolistical

Answer 7

如果您想在同一张表中添加列，那么您只需要这样做。现在，选项3可能是最好的选择，因为在此操作进行时，您仍然可以让数据库处于“活动”状态。如果您使用选项1，则在操作发生时该表将被锁定，然后您真的会陷入困境。

如果您并不介意该列是否在表中，那么我认为分段方法是下一个最佳选择。尽管如此，我真的尽量避免这样做（甚至不这样做），因为像Charles Bretana所说，您必须确保找到所有更新/插入该表的地方并进行修改。烦死了！

- Nick DeVore

Answer 8

我曾经遇到过类似的问题，选择了你提供的第二种方法。这种方式只需要20分钟，而另一种方式则需要32个小时！效率差别太大了，非常感谢你的建议。我写了一篇完整的博客文章，但这是其中重要的SQL代码：

Alter table MyTable
Add MyNewColumn char(10) null default '?';
go

update MyTable set MyNewColumn='?' where MyPrimaryKey between 0 and 1000000
go
update MyTable set MyNewColumn='?' where MyPrimaryKey between 1000000 and 2000000
go
update MyTable set MyNewColumn='?' where MyPrimaryKey between 2000000 and 3000000
go
..etc..

Alter table MyTable
Alter column MyNewColumn char(10) not null;

如果你感兴趣的话，这是相关博客文章的链接： http://splinter.com.au/adding-a-column-to-a-massive-sql-server-table

- Chris

Answer 9

我曾经遇到过类似的问题，我采用了修改后的第三种方法。在我的情况下，数据库处于简单恢复模式，并且要添加列的表没有被任何FK约束引用。

我没有创建一个具有相同架构并复制原始表内容的新表，而是使用了SELECT…INTO语法。

根据微软的说法(http://technet.microsoft.com/en-us/library/ms188029(v=sql.105).aspx)

SELECT...INTO的日志记录量取决于数据库中生效的恢复模式。在简单恢复模式或批量日志记录恢复模式下，批量操作的日志记录最小化。使用最小化日志记录，使用SELECT… INTO语句比创建表然后使用INSERT语句填充表更有效率。有关更多信息，请参阅可以最小化记录的操作。

步骤如下：

1.将旧表数据移动到新表，同时添加默认值的新列

 SELECT  table.*,   cast (‘default’ as nvarchar(256)) new_column
 INTO    table_copy 
 FROM    table

2. 删除旧表

 DROP TABLE  table

3. 重命名新创建的表

 EXEC sp_rename 'table_copy',  ‘table’

4. 在新表上创建必要的约束和索引

在我的情况下，这个表有超过1亿行数据，这种方法比第二种方法更快，并且日志空间增长最小。

- Tanya Kogan

---

您是否需要在新的影子表上创建所有外键约束？ - Rebecca

根据文档 https://msdn.microsoft.com/en-us/library/ms188029.aspx 中的“限制和限制条件”部分，SELECT INTO 不会自动创建 FK 约束。它们必须手动添加。 - Tanya Kogan

Answer 10

1）在表中添加带有默认值的列：

ALTER TABLE MyTable ADD MyColumn int default 0

2) 在表格中递增地更新数值（与被接受的答案效果相同）。根据您的环境调整正在更新的记录数量，以避免阻塞其他用户/进程。

declare @rowcount int = 1

while (@rowcount > 0)
begin           

    UPDATE TOP(10000) MyTable SET MyColumn = 0 WHERE MyColumn IS NULL       
    set @rowcount = @@ROWCOUNT

end

3) 修改列定义以要求非空。 在表格不使用的时候运行以下命令（或者安排几分钟的停机时间）。我已经成功地在拥有数百万记录的表格上使用过。

ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL

- hobbsenigma