如何在SQL Server中给列添加标识(identity)？

Question

如何在SQL Server中给列添加标识(identity)？

sqlsql-serversql-server-2008

11

我有一个大约有1000万行的表格。我们刚用SQL Server Management Studio从另一个数据库导入了它。它创建了表格，但是主键列上没有设置自增和主键。

我可以添加主键，但无法添加自增。当我在设计器中进行操作时，它总是超时。即使我将超时设置为0。

我需要创建另一列来设置主键和自增，然后复制旧数据，删除旧列并重命名新列。

有人能告诉我如何在不额外过热的情况下处理这样大的表格吗？

- German

像这样的东西让你超时了吗？alter table TableName add ID int identity(1, 1) primary key - Tim Lehner

1

你可以使用 ALTER TABLE ... SWITCH 来实现几乎瞬间完成此操作，即使是在一个有1000万行的表上。 - Martin Smith

可能是一个重复问题：如何在创建一个无数据损失的表后设置自动增量？ - Martin Smith

@TimLehner：可能不行——他说在可视化设计器中会超时，因为它试图以新格式重新创建表并复制所有数据——总共有1000万行......但使用你的ALTER TABLE语句，应该很容易解决！ - marc_s

4个回答

4

您可以向现有列添加 IDENTITY。这很容易实现。在 SSMS 中，只需转到工具-->选项-->设计师-->表和数据库设计师，并取消选中防止需要重新创建表的更改的选项。现在在设计模式下添加所需列的自增属性并保存即可。

- user2754364

4

好的。我已经成功将标识添加到了现有的1000万行主列中，用时约30分钟。

具体步骤如下：

将数据库更改为单用户模式（确保没有其他连接到数据库，可以引起锁定）
以设计者模式打开表格
进行更改。不要保存
单击生成更改脚本按钮（通常在对象资源管理器上方右侧）
复制生成的脚本
关闭设计窗口（一次只能运行一个实例）
打开新窗口
执行脚本
完成。现在您的列已经有了标识 :)

- German

1

请检查脚本。我确信它会创建一个新表，将所有数据复制到新表中，然后删除原始表。 - Bill

1

如果你按照我的链接做的话，30秒就能搞定了！ - Martin Smith

2

通过使用选项set identity_insert在insert期间设置identity列是一种方法。例如，要将此表中的id更改为identity：

create table YourTable (id int, value varchar(50))

您可以使用这个脚本：

-- Create empty table as a copy of the original
select top 0 * into YourTable_New from YourTable

-- Drop the old ID column
alter table YourTable_New drop column id

-- Add a new ID column with identity
alter table YourTable_New add id int identity

-- Copy the old values into the identity column
set identity_insert YourTable_New on 
insert YourTable_New (id, value) select id, value from YourTable
set identity_insert YourTable_New off

-- Drop the old table and rename the new one
drop table YourTable
exec sp_RENAME 'YourTable_New' , 'YourTable'

- Andomar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- marc_s · Accepted Answer

你无法将IDENTITY添加到现有列中。这是不可能的。

你需要创建一个INT IDENTITY类型的新列，然后删除你不再需要的旧列（如果需要，可能还需要将新列重命名为旧名称）

另外：我不建议在可视化设计器中进行此操作- 这将尝试使用新结构重新创建表格，复制所有数据（全部1000万行），然后删除旧表格。

使用T-SQL语句会更加高效- 这将进行“原地”更新，非破坏性（不会丢失数据），并且不需要在此过程中复制约10万行...

ALTER TABLE dbo.YourTable
  ADD NewID INT IDENTITY(1,1) NOT NULL

当您向表中添加一个类型为INT IDENTITY的新列时，它将自动填充连续的数字。您无法阻止这种情况发生，也不能在以后更新这些值。

总的来说，这两个选项都不是非常有用 - 您可能最终会得到不同的ID值....要正确地执行此操作，您需要:

预先创建具有正确结构和已设置IDENTITY的新表
然后对该表启用SET IDENTITY_INSERT (yourtable) ON，以允许插入标识列的值
将数据从原始源复制到该表
再次关闭标识插入：SET IDENTITY_INSERT (yourtable) OFF

只有使用这种方法，您才能在新表的ID标识列中获取相同的ID。