如何向大型 SQL Server 表中添加列？

Question

我在生产中有一张拥有数百万行的SQL Server表，现在需要向其中添加一列，或者更准确地说，需要向该表所代表的实体添加一个字段。

从语法上讲，这并不是什么问题，如果这个表没有那么多行并且不在生产环境中，这将很容易。

实际上，我需要的是行动方案。有很多网站拥有极大的表格，他们必须不时地添加字段。他们如何在不造成相当长时间的停机的情况下完成呢？

还有一件事情，我不想让该列允许为空，这意味着我需要有一个默认值。

因此，我要么需要找出一种快速添加带有默认值列的方法，要么需要想办法稍后更新该列，然后设置该列不允许为空。

- Jonathan Beerhalter

Answer 1

ALTER TABLE table1 ADD
  newcolumn int NULL
GO

不应该花费那么长时间...花费时间较长的是在其他列中间插入列...因为引擎需要创建一个新表并将数据复制到新表中。

- Nestor

---

没错，只要该列的值为NULL，添加会非常快。但是如果我设置默认值，就需要很长时间。因此，我真正需要考虑的问题是如何向该列添加默认值。 - Jonathan Beerhalter

添加列后，执行相对较小的UPDATE批处理以使用默认值填充列。这样可以防止任何明显的减速。 - Agent_9191

谢谢Agent_9191，这似乎是一个相当不错的方法。 - Jonathan Beerhalter

为什么提问者明确表示希望设置默认值，但这个回答被接受了，我不理解。 - B T

如果新列是主键的一部分，则该方法无效。 - Carlo V. Dango

Answer 2

我不希望该列允许为空，这意味着我需要有一个默认值。在 SQL Server 2012 中（但只适用于企业版），向任何数量的行（甚至是数十亿行）添加带有默认约束的 NOT NULL 列变得更加容易，因为它们允许它成为在线操作（在大多数情况下），对于现有行，该值将从元数据中读取，并且在更新行或聚集索引重建之前实际上不会存储在行中。以下摘自 MSDN ALTER TABLE 页面的相关部分，而不是转述更多内容： 在线添加NOT NULL列

从SQL Server 2012企业版开始，如果默认值是运行时常量，则添加具有默认值的NOT NULL列将成为在线操作。这意味着无论表中有多少行，该操作都会几乎瞬间完成。这是因为在操作期间不更新表中现有的行；相反，仅在表的元数据中存储默认值，并且在访问这些行的查询中根据需要查找该值。此行为是自动的；除了ADD COLUMN语法之外，不需要其他语法来实现在线操作。运行时常量是一个表达式，在表中的每一行运行时产生相同的值，而不考虑其确定性。例如，常量表达式“My temporary data”或系统函数GETUTCDATETIME()都是运行时常量。相比之下，NEWID()或NEWSEQUENTIALID()函数不是运行时常量，因为对于表中的每一行都会生成唯一值。始终以离线方式执行具有非运行时常量的默认值的NOT NULL列的添加，并且在操作期间获取排他锁（SCH-M）。

虽然现有的行引用存储在元数据中的值，但是对于未指定该列的另一个值的任何新行，都将在行上存储默认值。当更新行时（即使在UPDATE语句中没有实际列指定）或重建表或聚集索引时，将元数据中存储的默认值移动到现有行中。

不能在在线操作中添加类型为varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography或CLR UDTS的列。如果这样做会导致最大可能的行大小超过8,060字节限制，则无法在线添加列。此时，将以离线方式添加该列。

- Solomon Rutzky

---

SQL Server 2012标准版本中有一个空列，包含14M行数据，高并发24x7运行。这是否会因模式锁定而导致引人注目的停机时间？ - Horaciux

@Horaciux 一个NULL列而不是NOT NULL？如果我理解正确，那就不是问题。这只是元数据，而且非常瞬间。在SQL Server 2012之前，如果要添加一个列而不阻塞任何内容，唯一的方法是将其添加为NULL，但是然后您必须通过SQL Agent作业或每个UPDATE的3000行来填充它（以避免锁定升级）。所以，在我的经验中，您不需要担心NULL列。 - Solomon Rutzky

Answer 3

保持连续性运行的唯一真正解决方案是冗余。

我认可@Nestor的回答，即在SQL Server中添加新列不应该花费很长时间，但仍然可能导致生产系统不可接受的停机。一个替代方案是在平行系统中进行更改，然后一旦操作完成，将新系统与旧系统交换。

例如，如果您需要添加一列，可以创建表的副本，然后将该列添加到该副本中，然后使用sp_rename()将旧表移开并将新表放置在原位。

如果有指向此表的引用完整性约束条件，则这可能会使交换变得更加棘手。在交换表时，您可能必须暂时删除约束条件。

对于某些复杂升级，您可以在单独的服务器主机上完全复制数据库。一旦准备就绪，只需交换两个服务器的DNS条目，就完成了！

我曾在1990年代支持一家股票交易公司，他们始终运行三个重复的数据库服务器。这样，他们可以在一个服务器上实施升级，同时保留一个生产服务器和一个故障转移服务器。他们的操作有一个标准程序，每天将三台机器轮流运行生产、故障转移和维护角色。当他们需要升级硬件、软件或更改数据库模式时，需要三天时间才能通过他们的服务器传播该变化，但他们可以在不中断服务的情况下完成。这都要归功于冗余。

- Bill Karwin

---

你在维护期间如何追赶落后的交易？使用标准复制吗？ - Eric J.

股票交易所不需要全天候运营。他们在收盘时关闭。 - Bill Karwin

噢 :-) 如何处理全天候系统的想法？ - Eric J.

对的，这个问题必须通过同步数据增量来解决。复制和日志记录是常见的解决方案。不过这是一个相当广泛的主题。我的一般观点是高可用性和冗余性是相辅相成的。 - Bill Karwin

当您生成更改脚本而不是保存在工具上并等待操作完成时，这就是SSMS所做的事情。 - Teoman shipahi

Answer 4

"添加该列，然后执行相对较小的UPDATE批处理以使用默认值填充该列。这样可以防止任何明显的减速。之后，您需要将该列设置为NOT NULL，这将触发一个大事务。因此，在此之前，所有操作都会非常快，所以您可能实际上并没有获得太多收益。我只知道这是来自第一手经验。您可能希望将当前表从X重命名为Y。您可以使用此命令sp_RENAME '[OldTableName]'，'[NewTableName]'来完成此操作。重新创建新表作为X，并将新列设置为NOT NULL，然后从Y到X批量插入，并在插入新列时包含默认值，或在重新创建表X时在新列上放置默认值。我曾在拥有数亿行的表上进行了此类更改。它仍然需要一个多小时，但它没有使我们的事务日志爆满。当我尝试仅更改表中的所有数据的列为NOT NULL时，它花费了超过20个小时，然后我终止了该进程。您是否已经测试过仅添加列、填充数据并将列设置为NOT NULL？因此，最终我认为并没有什么神奇的解决方法。"

- Kuberchaun

Answer 5

将数据选择插入到新表并重命名。例如，向表A添加列i：

select *, 1 as i
into A_tmp
from A_tbl

//Add any indexes here

exec sp_rename 'A_tbl', 'A_old'
exec sp_rename 'A_tmp', 'A_tbl'

这种方法应该很快，而且不会像批量插入一样触及您的事务日志。我今天刚用这种方法处理了一个7000万行的表，在不到2分钟内完成。

如果您需要在线操作（在 select into 和重命名之间可能有变化），可以将其包装在事务中。

- eric

---

我很难理解这个。你从 A_tbl 插入到 A_tmp，但是然后你将 A_tbl 重命名为 A_old，再将 A_old 重命名为 A_tbl。最后一个重命名不应该是从 A_tmp 到 A_tbl 吗？ - Rebecca

@Junto 是的，我修好了。 - John Zabroski

Answer 6

另一种技术是将该列添加到一个新的相关表中（假设您可以通过给FK一个唯一索引来强制执行一对一关系）。然后，您可以分批填充此表，然后在任何需要出现数据的地方添加连接到此表。请注意，我只会考虑将其用于原始表上不希望在每个查询中使用的列，或者如果我的原始表的记录宽度过大，或者如果我正在添加多个列。

- HLGEM