SQL Server 2017+：添加非空列仅为现有行赋值的优雅DDL方案？

核心结论 #

在SQL Server的DDL语法中，不存在单行语句能直接实现该需求——即添加非空列时仅为现有行设置初始值，同时确保新插入的行必须显式指定该列的值（不能依赖默认约束）。

现有方案分析 #

你提到的两种方案是当前可行的实现方式，其中方案1在性能和脚本健壮性上更优：

方案1：添加带命名默认约束的列，随后删除约束

这种方式利用了SQL Server 2016及以上版本的优化：当添加带常量默认值的非空列时，操作仅修改元数据，不会触发全表扫描填充数据（后台异步完成），性能远优于全表更新。显式命名约束也避免了查询系统表获取约束名的麻烦，适合无人值守脚本。

-- 添加带命名默认约束的非空列，自动填充现有行
ALTER TABLE MyTable ADD Foo INT NOT NULL CONSTRAINT DF_Foo DEFAULT(0);
-- 删除默认约束，确保新行必须显式设置Foo的值
ALTER TABLE MyTable DROP CONSTRAINT DF_Foo;

方案2：先添加可空列，填充后改为非空

该方案需要执行全表更新操作，大表场景下性能极差，且中间状态（列处于可空状态）可能引入并发问题（比如这段时间插入的行未设置该列值），除非用事务包裹整个流程。

-- 添加可空列
ALTER TABLE MyTable ADD Foo INT NULL;
-- 全表填充初始值
UPDATE MyTable SET Foo = 0;
-- 修改列为非空
ALTER TABLE MyTable ALTER COLUMN Foo INT NOT NULL;

总结 #

尽管没有单行语句的完美方案，但方案1是当前最优选择——性能高效、脚本可控，能满足你“仅为现有行设置值，新行需显式指定”的需求。

内容的提问来源于stack exchange，提问作者Seva Alekseyev