如何在已创建的表上设置Identity(1,1)？不重建表也不新增列

嘿，作为SQL新手碰到这个需求挺常见的！首先得明确：SQL Server没有直接的ALTER TABLE命令可以把现有列改成IDENTITY列，但我们可以通过一些间接操作来实现，而且不需要手动重建整张表结构，也不用新增列——不过操作前一定要先备份你的表数据，避免意外！

前提条件 #

• 目标列必须是整数类型（比如int、bigint、smallint），不能是字符串或其他类型
• 目标列当前不能有NULL值，也不能有重复值（IDENTITY列要求唯一且非空）
• 操作期间表会被锁定，建议在业务低峰期执行

具体步骤 #

假设你的表叫YourTable，要设置为IDENTITY的列叫YourColumn：

1. 创建临时表，复制原表结构并设置IDENTITY
   先创建一个和原表结构完全一致的临时表，但把目标列设为IDENTITY(1,1)：

   SELECT * INTO #TempTable FROM YourTable WHERE 1=0; -- 复制表结构，不复制数据
   ALTER TABLE #TempTable ALTER COLUMN YourColumn INT IDENTITY(1,1);
   

   （如果原列是bigint或其他整数类型，记得替换掉语句里的INT）

2. 开启IDENTITY_INSERT，导入原表数据
   因为IDENTITY列默认不允许手动插入值，所以先开启IDENTITY_INSERT权限，再把原表数据导入临时表：

   SET IDENTITY_INSERT #TempTable ON;
   -- 必须明确列出所有列，不能用SELECT *
   INSERT INTO #TempTable (YourColumn, Column1, Column2, ...)
   SELECT YourColumn, Column1, Column2, ... FROM YourTable;
   SET IDENTITY_INSERT #TempTable OFF;
   

3. 替换原表
   先把原表重命名作为备份，再将临时表重命名为原表的名字：

   -- 把原表改名为旧表，留作备份
   EXEC sp_rename 'YourTable', 'YourTable_Old';
   -- 将临时表重命名为原表名，完成替换
   EXEC sp_rename '#TempTable', 'YourTable';
   

4. 恢复索引、约束和触发器（如果有）
   SELECT INTO只会复制表结构和数据，不会带索引、主键约束、触发器这些对象。你需要从原备份表（YourTable_Old）里把这些对象重新创建到新表上。比如恢复主键约束：

   ALTER TABLE YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY (YourColumn);
   

   可以通过以下查询获取原表的约束/索引信息：

   -- 查询索引信息
   SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('YourTable_Old');
   -- 查询主键/外键约束
   SELECT * FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('YourTable_Old');
   

5. 验证IDENTITY是否生效
   插入一条不指定目标列的测试数据，检查是否自动生成自增值：

   -- 不要指定YourColumn的值
   INSERT INTO YourTable (Column1, Column2) VALUES ('测试内容1', '测试内容2');
   SELECT * FROM YourTable;
   

额外说明 #

如果你的数据库是MySQL，操作会简单很多，直接用这条语句即可（对应SQL Server的IDENTITY）：

ALTER TABLE YourTable MODIFY COLUMN YourColumn INT AUTO_INCREMENT;

内容的提问来源于stack exchange，提问作者Ashish Gupta