我需要在数据库中的一张表格中添加一个新列。该表格包含约1.4亿行数据，我不确定如何在不锁定数据库的情况下进行操作。
由于数据库正在生产环境中使用，因此必须确保这一过程尽可能顺利。
我阅读了很多资料，但从未得到确切答案，是否会存在风险。新列可为空且默认值可以为NULL。据我所知，如果新列需要设置默认值，则问题较大。
我真的很希望能得到直接的答案。这个操作是否可行？
4个回答
9
没问题，这是完全可行的。
在一张表中添加一个允许NULL且无默认值的列不需要长时间锁定来向表中添加数据。
如果你提供了一个默认值，那么SQL Server必须去更新每一条记录以便将新的列值写入行中。
总体工作原理：
+---------------------+------------------------+-----------------------+
| Column is Nullable? | Default Value Supplied | Result |
+---------------------+------------------------+-----------------------+
| Yes | No | Quick Add (caveat) |
| Yes | Yes | Long running lock |
| No | No | Error |
| No | Yes | Long running lock |
+---------------------+------------------------+-----------------------+
需要注意的地方：
我想不起来当你添加一个列导致NULL位图大小扩展时会发生什么。我想说，NULL位图表示当前行中所有列的可空性，但我不能保证这是绝对正确的。
编辑 -> @MartinSmith指出，只有在更改行时，NULL位图才会扩展，非常感谢。然而，正如他所指出的，如果行的大小在SQL Server 2012中超过了8060字节的限制，则可能仍需要长时间运行锁定。再次感谢 * 2。
第二个需要注意的地方：
测试一下。
第三个也是最后一个需要注意的地方：
真的，测试一下。
- Matt Whitfield
8
1是的，元数据只有变化，
NULL_BITMAP 才会在下次更新行时扩展。 - Martin Smith@MartinSmith - 我希望有一天能和你一起工作。 :) 谢谢并表示非常尊敬。 - Matt Whitfield
我可能需要补充一下，我正在运行SQL Server 2008，这会有影响吗？ - FREDRIK
那么换句话说，这个操作没有风险吗？我明天将对数据库进行完整备份，并在备份上添加新列以查看其反应并估计时间。最好安全第一；）特别是当数据库处于生产状态时。 - FREDRIK
1换句话说，不应该有风险。但是有两个警告，出于某种原因要求“测试它” - 正如您所说，安全第一。 - Matt Whitfield
显示剩余3条评论
6
我的例子是如何在数千万行的表中添加新列，并使用默认值填充，而不会出现长时间运行的锁定问题。
USE [MyDB]
GO
ALTER TABLE [dbo].[Customer] ADD [CustomerTypeId] TINYINT NULL
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_CustomerTypeId] DEFAULT 1 FOR [CustomerTypeId]
GO
DECLARE @batchSize bigint = 5000
,@rowcount int
,@MaxID int;
SET @rowcount = 1
SET @MaxID = 0
WHILE @rowcount > 0
BEGIN
;WITH upd as (
SELECT TOP (@batchSize)
[ID]
,[CustomerTypeId]
FROM [dbo].[Customer] (NOLOCK)
WHERE [CustomerTypeId] IS NULL
AND [ID] > @MaxID
ORDER BY [ID])
UPDATE upd
SET [CustomerTypeId] = 1
,@MaxID = CASE WHEN [ID] > @MaxID THEN [ID] ELSE @MaxID END
SET @rowcount = @@ROWCOUNT
WAITFOR DELAY '00:00:01'
END;
ALTER TABLE [dbo].[Customer] ALTER COLUMN [CustomerTypeId] TINYINT NOT NULL;
GO
ALTER TABLE [dbo].[Customer] ADD [CustomerTypeId] TINYINT NULL只改变元数据（Sch-M锁），锁定时间不取决于表中行数。
之后，我通过默认值以小批量（5000行）填充一个新列。每个循环后等待一秒钟，以避免太过激烈地阻塞表。我有一个int列“ID”作为主聚集键。
最后，当所有新列都被填满后，我将其更改为NOT NULL。
- AlexK
1
2你应该描述你试图解决的问题。（我可以从代码中推断出来，但对于普通读者来说并不明显。） - Klas Lindbäck
5
由于许多其他因素的影响，没有人能够确定操作需要花费多长时间。
您不应该担心操作本身，因为SQL Server正在正确执行一切：
数据库引擎在表数据定义语言（DDL）操作期间（例如添加列或删除表）使用模式修改（Sch-M）锁定。在保持此锁定的时间内，Sch-M锁定会阻止对表的并发访问。这意味着Sch-M锁定会阻塞所有外部操作，直到释放锁定。
我从未对如此多的数据执行过ALTER操作，唯一的建议是在没有太多数据库连接时进行操作（在晚上进行）。
编辑：
这里 您可以找到有关您问题的更多信息。一般来说，Matt Whitfield 是正确的。
而当新列可空且具有NULL默认值时，表的元数据记录了新列存在但可能不在记录中的事实。这就是为什么null位图也记录了该特定记录中的列数。SQL Server可以计算出记录中是否存在某个列。因此，这不是大小数据操作——当添加新列时，现有的表记录不会更新。只有当它们更新为其他操作时，记录才会更新。
- gotqn
5
那个编辑使一切变得清晰。显然，SQL Server不关心值是否为NULL（无需重新计算），只有在添加列后更改列时才考虑新列？ - FREDRIK
是的，看起来是这样。但如果您检查
Martin Smith提供的链接，您会发现有一个例外。我不熟悉那里描述的情况，但据我所读，我认为最好的做法是：1.在本地机器上创建表格；2.添加一些记录；3.更改表格以添加新列；4.检查链接中的情况是否出现。 - gotqn当然，在生产数据库上执行之前，我会进行一些测试。但是据我所知，这仅适用于SQL Server 2012（根据他的写作，由于某种原因我无法访问链接）？我目前正在运行2008。 - FREDRIK
1@FREDRIK 这是链接 http://rusanu.com/2012/02/16/adding-a-nullable-column-can-update-the-entire-table/ 我认为在进行 ALTER 操作之前值得研究/测试这种情况。 - gotqn
是的，绝对没错。感谢您的帮助，链接现在可以使用了。我会阅读它并进行一些测试，明天再回来分享测试结果。 - FREDRIK
1
我通常使用一种方法 - 导出该表并在本地创建新列，重新命名表名，然后导入表格，并仅将现有表格重命名并将第一个表格名称转换为原始名称。
- Suleman Ahmad
2
有1.4亿行，那不会很流畅。 - senseiwu
@zencv：我已经用3000-4000万完成了这项技术，但你说得对，需要在1.4亿上进行一些特别的注意。 - Suleman Ahmad
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
create as select重新创建表格更快吗？我们在 Oracle 数据库中使用这种方法。但是您需要重新创建所有约束。 - alko