如何为non-nullable列设置默认值为SELECT查询的TOP 1结果？

你遇到的问题是SQL Server不允许在ALTER TABLE语句中直接使用变量作为默认值，这里提供两种可行的解决方案：

方法1：使用动态SQL拼接具体值 #

通过动态SQL将获取到的最新user_id值直接拼接到ALTER TABLE语句中，绕开变量的限制：

DECLARE @latestuserid AS int
DECLARE @sql NVARCHAR(MAX)

-- 获取Employees表中最大的user_id
SELECT TOP 1 @latestuserid = user_id FROM Employees ORDER BY user_id DESC

-- 拼接ALTER TABLE语句
SET @sql = N'ALTER TABLE Control ADD nextuserid INT NOT NULL DEFAULT (' + CAST(@latestuserid AS NVARCHAR(10)) + ')'

-- 执行动态SQL
EXEC sp_executesql @sql

这种方式执行时，实际传入ALTER TABLE的是具体的数值，完全符合语法要求。

方法2：分步操作（适合不需要后续默认值的场景） #

如果只是需要给现有行填充值，后续插入不需要自动使用这个默认值，可以分三步操作：

-- 1. 先添加允许为空的列
ALTER TABLE Control ADD nextuserid INT NULL

-- 2. 将所有现有行的列值更新为最新的user_id
UPDATE Control SET nextuserid = (SELECT TOP 1 user_id FROM Employees ORDER BY user_id DESC)

-- 3. 修改列属性为非空
ALTER TABLE Control ALTER COLUMN nextuserid INT NOT NULL

如果后续插入也需要默认值，还是推荐方法1，因为直接在ALTER TABLE中用子查询设置默认值是不被支持的。

内容的提问来源于stack exchange，提问作者SP Alonday