在SQL Server中如何为大表添加带默认值非空列而不锁死？-SQL-PHP中文网

直接加not null列并指定默认值会锁表，因sql server需逐行更新所有现有数据页、持sch-m锁；可行方案是三步法：先加null列、再分批更新、最后设not null约束。

在sql server中如何为大表添加带默认值非空列而不锁死？

直接加 NOT NULL 列并指定默认值，在大表上会锁表、阻塞所有读写，且执行时间不可控——除非你用的是 SQL Server 2012+ 并启用 ONLINE = ON（仅限企业版），否则别指望“不锁死”。

为什么 ALTER TABLE ADD COLUMN WITH DEFAULT 会锁死大表？

SQL Server 在添加带默认值的 NOT NULL 列时，会为**现有每一行**填充该默认值。这意味着：它必须逐行更新整张表的数据页，触发完整日志记录、可能引发大量页分裂，并持有 SCH-M（架构修改）锁直到操作完成。对千万级以上行的表，这个过程可能持续数分钟甚至更久，期间 SELECT、INSERT、UPDATE 全部被阻塞。

错误现象：ALTER TABLE ... ADD column_name INT NOT NULL DEFAULT 0 执行卡住，sys.dm_exec_requests 中看到 wait_type = 'LCK_M_SCH_M'
即使默认值是常量（如 0 或 GETDATE()），SQL Server 仍做全表更新（2016 及以前版本）
SQL Server 2012+ 引入了“元数据默认值”优化，但仅适用于 NULL 列 + DEFAULT 约束的组合，不适用于 NOT NULL 场景

真正可行的三步法：先加可空列，再填值，最后设非空

这是兼容所有版本、可控性最强的方式，核心是把“全表更新”拆成可分批、可暂停、可监控的操作。

第一步：添加允许 NULL 的列
ALTER TABLE dbo.Orders ADD status_code TINYINT NULL;
第二步：分批更新值（避免长事务和日志暴涨）
UPDATE TOP (10000) dbo.Orders SET status_code = 1 WHERE status_code IS NULL;，循环执行直到 @@ROWCOUNT = 0
第三步：添加 NOT NULL 约束（此时所有行已非空，只需校验，极快）
ALTER TABLE dbo.Orders ALTER COLUMN status_code TINYINT NOT NULL;

注意：第二步若在高并发写入场景下执行，建议配合 WHERE 条件避开热点数据段（例如按主键范围或时间分区），并避免在业务高峰执行。

企业版用户可考虑 ONLINE 操作（但仍有前提）

SQL Server 2014+ 企业版支持 ONLINE = ON 的 ADD COLUMN，但仅当默认值是**运行时常量**（如 0、'abc'）且**不涉及计算列或函数**（如 GETDATE()、NEWID()）时才真正在线。

正确示例：ALTER TABLE dbo.Orders ADD is_archived BIT NOT NULL DEFAULT 0 WITH (ONLINE = ON);
错误示例：DEFAULT GETDATE() 或 DEFAULT (YEAR(GETDATE())) 会导致 ONLINE = ON 失效，退化为传统锁表模式
即便成功，ONLINE = ON 仍需额外内存与 tempdb 空间，且在极端负载下可能因锁争用失败（报错 Msg 10605）

别踩这些坑

很多方案看似巧妙，实则埋雷：

用 ADD COLUMN ... NULL DEFAULT ... 再建 CHECK 约束？不行——CHECK 不阻止 NULL 插入，无法等价于 NOT NULL
依赖 IDENTITY 或 COMPUTED 列模拟默认？语义不符，且无法满足业务层对“显式默认值”的要求
误信“SQL Server 2022 默认在线”——目前（2026年）仍无此特性；ONLINE 仍是企业版专属，且仅覆盖部分 DDL 场景
忽略填充因子和索引碎片：批量更新后，聚集索引页可能严重分裂，后续应评估是否需要 REORGANIZE 或 REBUILD