如何在 SQL Server 中向现有列添加 identity 属性

Question

在SQL Server中（在我的情况下，是2005），如何使用T-SQL将身份属性添加到现有表列中？

类似于：

alter table tblFoo 
    alter column bar identity(1,1)

- DrFredEdison

---

有趣的是，SQL Server 2005 Compact Edition 有这个语法，但服务器版没有... - Hapkido

Azure SQL 也没有这个语法。 - Larry Smithmier

赏金将授予能够完成此任务的TSQL脚本。(sig: spSetIdentity('TableName', 'Id')) - Sam Saffron

@Martin，当然2008也是一个有效的要求（尽管最初的问题是2005）。 - Sam Saffron

如果您使用的SKU支持分区（这只需要进行元数据更改即可，参考此链接：https://dev59.com/11TTa4cB1Zd3GeqPwdwR#6094907）。 - Martin Smith

Answer 1

我不相信你可以做到那样。最好的办法是创建一个新的身份列，并使用身份插入命令将数据复制过去（如果您确实想保留旧值）。

这里有一篇不错的文章，详细描述了该过程：

http://www.mssqltips.com/tip.asp?tip=1397

- JohnFx

---

@Sam，这个赏金是为了什么？保持列的顺序吗？如果是的话，您需要创建一个临时表，将所有数据复制到其中，然后重新创建具有正确列顺序的表并将数据复制回去。管理工作室可以为您完成此操作，但仅适用于您没有脚本的情况。Visual Studio DB Edition可以创建一个更改脚本，以处理重新排序列的问题... - Stefan Egli

@Brent - 这是可能的 - Martin Smith

@Martin - 你能不能少说一点具体的？;-) 举个例子怎么样？ - Brent Ozar

@Brent - 我的评论有一个超链接，但显示效果不太好！ - Martin Smith

@Martin - 哇，那是一些疯狂的特技驾驶！我不确定我会舒服地建议客户这样做，但这很令人印象深刻。 - Brent Ozar

Answer 2

Vikash提供的解决方案不可行；在SQL Management Studio（正如OP指定的）中会产生“语法错误”。 SQL Server的“紧凑版”支持这种操作的事实只是一个快捷方式，因为真正的过程更像是Robert和JohnFX所说的 - 创建重复表，填充数据，适当重命名原始和新表。

如果您想保留需要成为标识的字段中已存在的值，可以尝试以下方法：

CREATE TABLE tname2 (etc.)
INSERT INTO tname2 FROM tname1

DROP TABLE tname1
CREATE TABLE tname1 (with IDENTITY specified)

SET IDENTITY_INSERT tname1 ON
INSERT INTO tname1 FROM tname2
SET IDENTITY_INSERT tname1 OFF

DROP tname2

当然，删除并重新创建一个被实时代码使用的表（tname1）是不建议的！ :)

- NateJ

---

这也是使用Azure SQL表所需的。 - Larry Smithmier

只有创建另一个表是唯一的方法，因为如果您尝试创建另一列并重命名它，则无法使用旧数据更新标识列。谢谢。 - Mhmd

Answer 3

这个表是否已经有数据了？如果没有，就删除并重新创建该表。

如果表中已经存在值，那么这些值是不是你想要保留的值呢？

按照你的要求创建一个新表，将原表中的记录加载到新表中，并让数据库正常地填充identity列。然后将原始表重命名，将新表重命名为正确的名称 :)

最后，如果你想将某个列设为identity，而该列当前包含主键值并且已被其他表引用，那么你需要彻底重新考虑一下，确认这是否是你想要做的操作 :)

- Robert

Answer 4

1. Create a New Table

   SELECT * INTO Table_New FROM Table_Current WHERE 1 = 0;
   

2. Drop Column from New Table

   Alter table Table_New drop column id;
   

3. Add column with identity

   Alter table Table_New add id int primary key identity; 
   

4. Get All Data in New Table

   SET IDENTITY_INSERT Table_New ON;
   INSERT INTO Table_New (id, Name,CreatedDate,Modified) 
   SELECT id, Name,CreatedDate,Modified FROM Table_Current;
   SET IDENTITY_INSERT Table_New OFF;
   

5. Drop old Table

   drop table Table_Current;
   

6. Rename New Table as old One

   EXEC sp_rename 'Table_New', 'Table_Current';
   

- Anuj Kumar

Answer 5

除了通过SQL之外，没有直接的方法可以做到这一点：

A）通过SQL，例如：

-- make sure you have the correct CREATE TABLE script ready with IDENTITY

SELECT * INTO abcTable_copy FROM abcTable

DROP TABLE abcTable

CREATE TABLE abcTable -- this time with the IDENTITY column

SET IDENTITY_INSERT abcTable ON

INSERT INTO abcTable (..specify all columns!) FROM (..specify all columns!) abcTable_copy

SET INDENTITY_INSERT abcTable OFF

DROP TABLE abcTable_copy 

-- I would suggest to verify the contents of both tables 
-- before dropping the copy table

B) 通过MSSMS，它将在后台执行完全相同的操作，但输入错误的可能性更小。

• 在MSSMS对象资源管理器中，右键单击需要修改的表
• 选择“设计”，选择要添加IDENTITY的列
• 将标识设置从NO->YES（可能是种子）
• 按Ctr+S保存表

这将删除并重新创建包含所有原始数据的表。 如果您收到警告：

进入MSSMS 工具->选项->设计师->表和数据库设计师， 然后取消选中"防止保存需要重新创建表的更改"选项。

需要注意的事项：

1. 在执行此操作之前，请确保您的数据库有足够的磁盘空间
2. 数据库未被使用（特别是正在更改的表）
3. 在执行此操作之前，请务必备份您的数据库
4. 如果表中有大量数据（超过1G），请先在其他地方尝试，然后再在真实数据库中使用

- Milan

Answer 6

alter table tablename 
alter column columnname 
add Identity(100,1)

- Vikash

---

这个适用于你的列最大值为100的情况。第一个参数应该是等于你的列中最大值的值。 - Jim Counts

这个语法在 SQL Server 2008 R2 中也不起作用。 - Greg