logo标签列表

添加列到生产表

sql-serversql-server-2008-r2lockingalter-table
什么是在SQL Server 2008 R2上向大型生产表添加列的最佳方法？根据Microsoft的在线书籍： ALTER TABLE中指定的更改会立即实施。如果更改需要修改表中的行，则ALTER TABLE会更新行。 ALTER TABLE在表上获取模式修改锁，以确保在更改期间没有其他连接引用表的元数据，除了需要非常短的SCH-M锁的在线索引操作。

(http://msdn.microsoft.com/en-us/library/ms190273.aspx)

在一张有数百万行的大表上，这可能需要一些时间。难道只能停机处理吗？如何最好地处理这种情况？
1最近关于这个问题的文章：http://www.sqlservercentral.com/articles/Change+Tracking/74397/ - 8kb
4个回答
"这要看情况而定" "如果你添加的列不需要在行中添加数据，那么速度可能会很快。" "例如，添加一个int或char类型的列需要进行物理行移动。添加一个可为空的varchar列且没有默认值的话，通常不需要（除非NULL位图需要扩展）。" "你需要在生产环境的备份上尝试一下，以获得一个估计。" "如果你需要在一个拥有十亿行的表上创建一个新表、复制数据并重命名，那么重新添加索引和键可能会花费更长时间。" "我曾经修改过十亿行的表，在其中添加一个可为空的列只需要几秒钟。" "我有说过先备份吗？"
如果列是可为NULL的，影响应该可以忽略不计。如果列不能为NULL并且必须设置值，那么情况可能会有所不同。在这种情况下，我会采取以下措施，而不是一次性添加非空和默认约束，从而有效地向每一行添加数据： - 将列添加为可为NULL - 在大多数情况下应该很快 - 更新值为默认值 - 如果需要，可以分批进行更新 - 还可以使用此方法应用条件逻辑，其中某些行可能不会获得默认值 - 添加非空/默认约束 - 当所有数据都不为NULL时，这将更快，但仍然可以测量出来 同意 @gbn 的观点，您可以通过恢复生产环境的副本并在那里尝试来测试此方法...这样您就可以对时间有一个很好的了解（假设硬件相对类似），还可以看到对事务日志的影响。
关于最后一部分：“•添加非空/默认约束”我不确定这是否存在潜在问题...当MSSQL（甚至是2008R2）将非空列更改为null时，如果你进行跟踪，你会发现它实际上在底层对表的每一行进行了完整的更新，即update table1 set column1 = column1。我猜它以一种非常愚蠢的方式进行非空验证。这个事务的大小是表的两倍（前后页），所以对于一个DW表来说可能非常庞大。以前我们不得不将数据bcp导出，截断表，进行从null到非null的更改，然后再bcp导入。 - user7116
你考虑过以下几点吗： 1. 创建一个新表，包括对表定义的更改。 2. 在新表定义中插入从原始表中选择的数据。 3. 将原始表重命名为"_orig"，然后将新表重命名为原始表的名称。 这种方法的缺点是你必须在数据库中有足够的空间来进行此更改。你可能仍然需要对表进行读锁定，以防止任何脏读取。 然而，如果有机会或需要同时访问原始表，这样做可以最大程度地减少对最终用户的影响。它还应该能够最小化锁定持续时间。
你是不是需要一个写锁而不是读锁？用户可以看到旧表中的数据，但是你不希望他们提交任何更改，在缓冲交换完成后这些更改会被覆盖。 - Jon of All Trades
那是我以数据仓库的角度思考时的想法，变更可以更容易地受控制。在OLTP情况下，你是正确的，为了避免对表进行更改，需要写入锁定。 - RobPaller

我有一个特殊的异常，我觉得应该提一下。

使用SQL Server 2012 Enterprise及更高版本，在表中添加一个新的NOT NULL列，并且该列具有运行时常量，这是一个在线操作，可以立即完成，而且不受表中行数的影响。

关于此的更多信息可在MSDN中找到。

我将重现重要部分：

从SQL Server 2012 (11.x) Enterprise Edition开始，当默认值为运行时常量时，添加一个带有默认值的NOT NULL列是一个在线操作。这意味着无论表中有多少行，该操作几乎瞬间完成。因为在操作期间不会更新表中的现有行。相反，该默认值仅存储在表的元数据中，并且在访问这些行的查询中根据需要进行查找。