如何为SQL表的每条记录设置自增1的ID字段？

原代码问题说明 #

你写的WHILE循环存在两处关键问题：

1. 语法错误：UPDATE后应指定表名而非字段名，正确格式是UPDATE [production].[stocks]
2. 循环逻辑错误：WHILE [production].[stocks].[id] = 1无法作为循环条件——表中存在多条记录时，该条件无意义，要么直接不执行循环，要么会陷入无效循环（仅修改单条记录后就停止，其他记录的id根本不会被更新）

正确实现步骤 #

不需要用WHILE循环，用窗口函数ROW_NUMBER()可以高效完成连续ID赋值，后续再调整主键和外键：

1. 添加ID字段（若尚未添加）

如果表中还没有id字段，先执行添加：

ALTER TABLE [production].[stocks] ADD id INT;

2. 为所有记录赋值从1开始的连续ID

用ROW_NUMBER()按原复合主键排序生成连续值，一次性更新所有记录：

WITH StockIDs AS (
    SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY store_id, product_id) AS new_id
    FROM [production].[stocks]
)
UPDATE StockIDs
SET id = new_id;

3. 将ID设为主键并开启自增（可选，用于后续插入）

先把id设为非空，再设置主键约束；如果需要后续插入数据时自动自增，开启IDENTITY属性：

-- 设置id为非空
ALTER TABLE [production].[stocks] ALTER COLUMN id INT NOT NULL;
-- 添加主键约束
ALTER TABLE [production].[stocks] ADD CONSTRAINT PK_stocks_id PRIMARY KEY (id);
-- 开启自增（若需要后续自动生成ID）
ALTER TABLE [production].[stocks] ALTER COLUMN id INT IDENTITY(1,1);

4. 删除原复合主键并设置外键

首先需要查询原复合主键的约束名称（不同环境下名称可能不同）：

SELECT name 
FROM sys.key_constraints 
WHERE type = 'PK' 
  AND parent_object_id = OBJECT_ID('[production].[stocks]');

假设查询到的主键名称是PK_stocks_store_product，执行删除：

ALTER TABLE [production].[stocks] DROP CONSTRAINT PK_stocks_store_product;

然后分别给store_id和product_id添加外键约束（关联对应的父表，这里假设父表是[production].[stores]和[production].[products]）：

-- 给store_id添加外键
ALTER TABLE [production].[stocks] 
ADD CONSTRAINT FK_stocks_store 
FOREIGN KEY (store_id) REFERENCES [production].[stores](store_id);

-- 给product_id添加外键
ALTER TABLE [production].[stocks] 
ADD CONSTRAINT FK_stocks_product 
FOREIGN KEY (product_id) REFERENCES [production].[products](product_id);

补充说明 #

• 用ROW_NUMBER()比WHILE循环效率高得多，尤其是表中数据量较大时，避免了逐行更新的性能损耗
• 如果一开始就想让id字段自动自增，也可以在添加字段时直接设置IDENTITY，再通过SET IDENTITY_INSERT临时允许手动赋值：

ALTER TABLE [production].[stocks] ADD id INT IDENTITY(1,1);
SET IDENTITY_INSERT [production].[stocks] ON;

WITH StockIDs AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY store_id, product_id) AS new_id
    FROM [production].[stocks]
)
UPDATE StockIDs
SET id = new_id;

SET IDENTITY_INSERT [production].[stocks] OFF;

内容的提问来源于stack exchange，提问作者Nathan Shetzler