向大表添加默认值列

Question

我有一张包含四千万条记录的表格，我需要在该表格中添加一个新的INT NOT NULL列，并将默认值设置为0。

使用以下方式添加此列：

ALTER TABLE myTable ADD NewColumnID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 0

它将NewColumnID设置为0，适用于所有记录。当在拥有4000万条记录的生产表上运行此查询时，会花费很长时间吗？因为我知道执行以下操作需要非常长的时间:

UPDATE myTable SET NewColumnID = 0

更新：2020年1月5日：

我已经有一段时间没登录我的stackoverflow帐户了。我注意到了这个问题，这是我在2013年发布的。我因为这个问题受到了一些负面评价，现在我能够理解为什么会这样。我不得不反复阅读才明白自己当时在问什么以及答案如何适用。考虑到它已经被浏览超过6k次，也许（很抱歉是在7年之后）提供更多上下文信息是值得的。

允许我澄清一下问题：

我曾在银行软件提供商工作。我们在全球范围内有各种各样的客户，并正在推出一个大型软件更新，需要向我们的软件所使用的现有表中添加一个新列。这个特定的表通常相当大，具体取决于银行的规模。要求是，在第一次添加列时，为所有现有记录分配特定的ID，之后表中的所有新条目都将恢复为“0”的值。

因此......在测试阶段，我们注意到在升级脚本中包含以下内容需要花费近一小时处理4000万条记录:

ALTER TABLE myTable ADD NewColumnID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 0
UPDATE myTable SET NewColumnID = 50

上面的示例将添加新列，然后使用NewColumnID = 50更新所有现有记录。这是在其运行的硬件上花费近一个小时的原因。我知道这将根据客户基础设施的不同而大大变化。

问题的原因是想看看是否有更快的方法来完成上述操作。

让我澄清答案：

我完全理解为什么我的答案没有意义，但希望以下解释能够帮助：

您可以创建一个具有默认值的约束条件，并将要更新的值分配给所有现有记录，而不是添加列然后运行更新查询。创建列将自动插入此值：

ALTER TABLE myTable ADD CompanyID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 1 (takes about 1min to complete)

这本质上就是“一石二鸟”的效果。这个查询在大约1分钟内完成，而不是一个小时（在同一服务器上执行）。现在需要添加一个新列，其默认的id = x（每个客户端都不同）用于所有现有记录，因此恢复了 DEFAULT 0 约束，以便如果未传递值，则所有新插入的记录将假定为0。 因此引用：

然后只需将默认值设置为0即可。现在表中的所有记录都将具有CompanyID = 1。BOOM！

抱歉...这是7年前的事情，现在看起来真的很愚蠢 :) 但是谁知道，也许这可以帮助具有需要创造性hack的愚蠢要求的其他人 :)！

- FaNIX

---

你的问题是什么？ - M.Ali

它很长，看起来很正常。 - kmas

你可以...将该列添加为“NULL”，然后逐步更新表。 - Rob

当你知道更新字段需要时间时，如何不知道添加字段需要多长时间？ - D Stanley

Answer 1

主要问题是需要写入每一行，这会被记录为一个事务。为减少对日志的影响（如果日志文件大小设置不合理则效果更佳），最好尽可能地分解工作量：

1. add a NULlable column:

   ALTER TABLE dbo.myTable 
    ADD NewColumnID INT CONSTRAINT DF_Constraint DEFAULT 0;
   

2. Update the rows in a batch, say 10K rows at a time (this will minimize log impact - see this blog post for background):

   BEGIN TRANSACTION;
   SELECT 1;
   WHILE @@ROWCOUNT > 0
   BEGIN
     COMMIT TRANSACTION;
     BEGIN TRANSACTION;
   
     UPDATE TOP (10000) dbo.myTable SET NewColumnID = 0;
   END
   COMMIT TRANSACTION;
   

3. Add a check constraint (see these answers for more detail):

   ALTER TABLE dbo.myTable WITH CHECK
     ADD CONSTRAINT NewCol_Not_Null
     CHECK (NewColumnID IS NOT NULL); 
   

   You can save some time by using NOCHECK here, but as Martin explained in his answer, that is a one-time savings that could cost you plenty of headaches over the longer term.

这个问题之前已经在此问题中得到解答，但是那里的被接受的答案使用了NOCHECK，却没有任何免责声明说明不可信的约束会如何影响执行计划。

- Aaron Bertrand

Answer 2

不，从 SQL Server 2012 开始，这将是瞬时完成的:

在 SQL Server 2012 之前，当您向现有表中添加一个新的非 NULLable 列并设置默认值时，会发生数据大小操作: 每个表中的行都会更新以添加新列的默认值。对于小型表格来说，这并不重要，但对于大型表格来说，这可能��为问题，严重到完全阻止操作。但是，从 SQL Server 2012 开始，在大多数情况下，该操作是瞬间完成: 仅更改表元数据，而不会更新任何行。 http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

- Loren Paulsen

Answer 3

更新查询运行所需的时间完全取决于底层硬件，没有人能够给出明确的答案。更新4000万条记录可能需要几秒钟或几天，具体取决于机器的RAM数量和硬盘速度。话虽如此，如果像你展示的简单更新需要“非常长的时间”，那么修改语句很可能需要同样长或更长的时间。

- NotMe

Answer 4

如果您可以将NULL视为0，则可以将其添加为稀疏列 - 这样它就不需要添加任何存储空间，直到您实际添加值为止：

ALTER TABLE myTable 
ADD NewColumnID int SPARSE NULL 
CONSTRAINT DF_Constraint

否则，Aaron的方法可能是最不具侵入性的 - 它仍然需要很长时间，但可以“在线”完成。

- D Stanley

Answer 5

感谢Aaron提供的详细方法，但我进行了快速测试，简单的方法是按照以下步骤进行：

一些背景信息。我正在向一个现有的大型表中添加CompanyID。该ID指该记录所属的公司。默认值为0。但由于这将进入现有客户的生产数据库中，他们的公司ID为1。我们为所有客户编写了一个通用升级脚本，结果对于这个特定的客户，对该脚本进行了轻微修改，从而取得了显着的性能提升。

改为：

ALTER TABLE myTable ADD CompanyID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 0 (takes about 1min to complete)
UPDATE myTable SET CompanyID = 1 (will take over an hour)

我只是做这个：

ALTER TABLE myTable ADD CompanyID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 1 (takes about 1min to complete)

然后将默认值设置回0。现在，该表中的所有记录的CompanyID都将为1。嘭！

- FaNIX

---

添加一列并将所有4000万行设置为1只需要一分钟？你能解释一下为什么将默认值设置为1比将默认值设置为0要快得多吗？我很困惑。答案谈到了设置一个默认值并将所有数据更新为不同的值，而问题从未提到过值1。 - Aaron Bertrand

@AaronBertrand - 我猜他们在使用2012版本。虽然答案似乎解决了问题中未提及的情况。 - Martin Smith

这不是问题的答案。 - IsmailS

Answer 6

最好的方法是删除列并重新添加。这样可以将表中的任何触发器或约束条件的开销降到最低。

- Ajay Kumar