如何为 SQL Server 中的列添加标识？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8658498/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add identity to the column in SQL Server?
提问by German
I have a table about 10 million rows. We just imported it from another database with SQL Server Management Studio. It creates table but without identity and primary key on primary key column.
我有一个大约 1000 万行的表。我们刚刚使用 SQL Server Management Studio 从另一个数据库中导入了它。它创建表，但在主键列上没有标识和主键。
I could add the primary key but couldn't add identity. It's all the time timing out when I'm doing it in designer. Even I set time out settings to 0.
我可以添加主键，但无法添加身份。当我在设计师中做这件事时，它总是超时。即使我将超时设置设置为 0。
I need to create probably another column set primary key and identity, copy data from old, delete old column and rename new one.
我可能需要创建另一个列集主键和标识，从旧列复制数据，删除旧列并重命名新列。
Can anyone show me what will be the best way to do it for such big tables, without additional overheating?
谁能告诉我在没有额外过热的情况下为如此大的桌子做这件事的最佳方法是什么？
采纳答案by German
Okay. I was able to add identity to the existing primary column with 10 million records. Took me about 30 mins.
好的。我能够将身份添加到具有 1000 万条记录的现有主列。花了我大约 30 分钟。
The steps:
步骤：
Change database to single user mode (to make sure no other connections to databse, can cause lock)
Open table in designer mode
Make change. Do not save
Click Generate Change Script button (usually on the left right above the Object Explorer)
- Copy generated script
- Close designer window (you can run only one instance at the time)
- Open new window
Execute script
Done. Now your column has identity :)
将数据库更改为单用户模式（以确保没有其他连接到数据库，可能导致锁定）
以设计器模式打开表
做出改变。不要保存
单击 Generate Change Script 按钮（通常在 Object Explorer 上方的左侧）
- 复制生成的脚本
- 关闭设计器窗口（您一次只能运行一个实例）
- 打开新窗口
执行脚本
完毕。现在你的专栏有身份:)
回答by marc_s
You cannotadd IDENTITYto an existing column. It just cannot be done.
您不能添加IDENTITY到现有列。它只是无法完成。
You'll need to create a newcolumn of type INT IDENTITYand then drop the old column you don't need anymore (and possibly rename the new column to the old name - if that's needed)
您需要创建一个新类型的列，INT IDENTITY然后删除不再需要的旧列（并且可能将新列重命名为旧名称 - 如果需要）
Also: I would notdo this in the visual designer - this will try to recreate the table with the new structure, copy over all data(all 10 millions rows), and then drop the old table.
另外：我不会在视觉设计器中这样做 - 这将尝试使用新结构重新创建表，复制所有数据（所有 1000 万行），然后删除旧表。
It's much more efficient to use straight T-SQL statements - this will do an "in-place" update, non-destructive (no data is lost), and it doesn'tneed to copy around 10 millions rows in the process...
它更有效地使用直T-SQL语句-这将做一个“就地”升级，非破坏性的（没有数据丢失），它并不需要大约10百万行的过程中，复制.. .
ALTER TABLE dbo.YourTable
ADD NewID INT IDENTITY(1,1) NOT NULL
When you add a new column of type INT IDENTITYto your table, then it will be automatically populated with consecutive numbers. You cannot stop this from happening, and you also cannot update the values later on.
当您INT IDENTITY向表中添加一个新的类型列时，它将自动填充连续的数字。您无法阻止这种情况的发生，也无法在以后更新这些值。
Neither of those options is really very useful, in the end - you might end up with different ID values.... to do this right, you'd have to:
这两个选项都不是非常有用，最终 - 你可能会得到不同的 ID 值......要正确地做到这一点，你必须：
- create the new table ahead of time, with the proper structure and the
IDENTITYalready in place - then turn on
SET IDENTITY_INSERT (yourtable) ONon that table to allow values to be inserted into the identity column - copy over this data from the original source
- turn off identity insert again:
SET IDENTITY_INSERT (yourtable) OFF
- 提前创建新表，具有适当的结构并且
IDENTITY已经就位 - 然后打开
SET IDENTITY_INSERT (yourtable) ON该表以允许将值插入到标识列中 - 从原始来源复制此数据
- 再次关闭身份插入：
SET IDENTITY_INSERT (yourtable) OFF
Only with this approach will you be able to get the same ID's in an IDENTITY column in your new table.
只有使用这种方法，您才能在新表的 IDENTITY 列中获得相同的 ID。
回答by German
You can add IDENTITY to an existing column. It just can be done. in SSMS Just go to tools-->options-->Designers-->Table And Database Designers and uncheck option Prevent Saving changes that require table re-creation.
您可以将 IDENTITY 添加到现有列。它只是可以做到。在 SSMS 中只需转到工具--> 选项--> 设计器--> 表和数据库设计器，然后取消选中防止保存需要重新创建表的更改选项。
Now add identity in designer mode to the required column and save.
现在将设计器模式下的身份添加到所需的列并保存。
回答by Andomar
One way to set an identitycolumn is during an insertwith the option set identity_insert. For example, to change idin this table to identity:
设置identity列的一种方法是在insert使用选项期间set identity_insert。例如，要id在此表中更改为identity：
create table YourTable (id int, value varchar(50))
You could use this script:
你可以使用这个脚本：
-- 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'
