SQL 向现有列添加标识

Adding an identity to an existing column

提问by Kirschstein

I need to change the primary key of a table to an identity column, and there's already a number of rows in table.

我需要将表的主键更改为标识列，并且表中已经有许多行。

I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.

我有一个脚本来清理 ID，以确保它们从 1 开始是连续的，在我的测试数据库上运行良好。

What's the SQL command to alter the column to have an identity property?

更改列以具有标识属性的 SQL 命令是什么？

回答by John Sansom

You can't alter the existing columns for identity.

您不能更改现有的标识列。

You have 2 options,

你有2个选择，

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

1. 创建具有标识的新表并删除现有表

2. 创建具有标识的新列并删除现有列

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column.

方法 1.（新表）在这里您可以保留新创建的标识列上的现有数据值。

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can't retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

方法二（新建列）不能在新创建的标识列上保留现有的数据值，标识列将保存数字的序列。

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

有关更多详细信息，请参阅以下 Microsoft SQL Server 论坛帖子：

How to alter column to identity(1,1)

如何将列更改为标识(1,1)

回答by Justin Grant

In SQL 2005 and above, there's a trick to solve this problem without changing the table's data pages. This is important for large tables where touching every data page can take minutes or hours. The trick also works even if the identity column is a primary key, is part of a clustered or non-clustered index, or other gotchas which can trip up the the simpler "add/remove/rename column" solution.

在 SQL 2005 及更高版本中，有一个技巧可以在不更改表的数据页的情况下解决此问题。这对于触摸每个数据页可能需要几分钟或几小时的大表很重要。即使标识列是主键、是聚簇或非聚簇索引的一部分，或者其他可以绊倒更简单的“添加/删除/重命名列”解决方案的陷阱，该技巧也有效。

Here's the trick: you can use SQL Server's ALTER TABLE...SWITCHstatement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column.

诀窍是：您可以使用 SQL Server 的ALTER TABLE...SWITCH语句更改表的架构而不更改数据，这意味着您可以用相同的表架构替换具有 IDENTITY 的表，但没有 IDENTITY 列。同样的技巧可以将 IDENTITY 添加到现有列。

Normally, ALTER TABLE...SWITCHis used to efficiently replace a full partition in a partitioned table with a new, empty partition. But it can also be used in non-partitioned tables too.

通常，ALTER TABLE...SWITCH用于用新的空分区有效地替换分区表中的完整分区。但它也可以用于非分区表。

I've used this trick to convert, in under 5 seconds, a column of a of a 2.5 billion row table from IDENTITY to a non-IDENTITY (in order to run a multi-hour query whose query plan worked better for non-IDENTITY columns), and then restored the IDENTITY setting, again in less than 5 seconds.

我使用这个技巧在 5 秒内将 25 亿行表的 a 列从 IDENTITY 转换为非 IDENTITY（为了运行多小时查询，其查询计划对非 IDENTITY 效果更好）列），然后在不到 5 秒的时间内再次恢复 IDENTITY 设置。

Here's a code sample of how it works.

这是它如何工作的代码示例。

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

This is obviously more involved than the solutions in other answers, but if your table is large this can be a real life-saver. There are some caveats:

这显然比其他答案中的解决方案更复杂，但如果你的桌子很大，这可能是一个真正的救星。有一些注意事项：

• As far as I know, identity is the only thing you can change about your table's columns with this method. Adding/removing columns, changing nullability, etc. isn't allowed.
• You'll need to drop foriegn keys before you do the switch and restore them after.
• Same for WITH SCHEMABINDING functions, views, etc.
• new table's indexes need to match exactly (same columns, same order, etc.)
• Old and new tables need to be on the same filegroup.
• Only works on SQL Server 2005 or later
• I previously believed that this trick only works on the Enterprise or Developer editions of SQL Server (because partitions are only supported in Enterprise and Developer versions), but Mason G. Zhwiti in his comment below says that it also works in SQL Standard Edition too. I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE...SWITCH.

• 据我所知，身份是唯一可以使用此方法更改表列的内容。不允许添加/删除列、更改可空性等。
• 您需要在进行切换之前删除外键并在之后恢复它们。
• 与 WITH SCHEMABINDING 函数、视图等相同。
• 新表的索引需要完全匹配（相同的列、相同的顺序等）
• 旧表和新表需要在同一个文件组中。
• 仅适用于 SQL Server 2005 或更高版本
• 我以前认为这个技巧仅适用于 SQL Server 的企业版或开发版（因为分区仅在企业版和开发版中受支持），但 Mason G. Zhwiti 在下面的评论中说它也适用于 SQL 标准版。我认为这意味着对 Enterprise 或 Developer 的限制不适用于 ALTER TABLE...SWITCH。

There's a good article on TechNetdetailing the requirements above.

TechNet 上有一篇很好的文章详细介绍了上述要求。

UPDATE - Eric Wuhad a comment below that adds important info about this solution. Copying it here to make sure it gets more attention:

更新 - Eric Wu在下面发表了评论，添加了有关此解决方案的重要信息。复制到这里以确保它得到更多关注：

There's another caveat here that is worth mentioning. Although the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT('<newTableName>')immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspxfor more info.

这里还有一个值得一提的警告。尽管新表很乐意从旧表接收数据，并且所有新行都将按照标识模式插入，但如果所述列是主键，它们将从 1 开始并可能中断。考虑DBCC CHECKIDENT('<newTableName>')在切换后立即运行。有关详细信息，请参阅msdn.microsoft.com/en-us/library/ms176057.aspx。

If the table is actively being extended with new rows (meaning you don't have much if any downtime between adding IDENTITY and adding new rows, then instead of DBCC CHECKIDENTyou'll want to manually set the identity seed value in the new table schema to be larger than the largest existing ID in the table, e.g. IDENTITY (2435457, 1). You might be able to include both the ALTER TABLE...SWITCHand the DBCC CHECKIDENTin a transaction (or not-- haven't tested this) but seems like setting the seed value manually will be easier and safer.

如果表正在积极地扩展新行（这意味着在添加 IDENTITY 和添加新行之间没有太多停机时间，那么DBCC CHECKIDENT您不需要手动将新表架构中的身份种子值设置为大于表中最大的现有 ID，例如IDENTITY (2435457, 1)。您可能能够在事务中同时包含 theALTER TABLE...SWITCH和 the DBCC CHECKIDENT（或不包括 - 尚未对此进行测试），但似乎手动设置种子值会更容易和更安全。

Obviously, if no new rows are being added to the table (or they're only added occasionally, like a daily ETL process) then this race condition won't happen so DBCC CHECKIDENTis fine.

显然，如果没有新行被添加到表中（或者它们只是偶尔添加，比如每天的 ETL 过程），那么这种竞争条件就不会发生，所以DBCC CHECKIDENT很好。

回答by marc_s

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.

您不能将列更改为 IDENTITY 列。您需要做的是创建一个从一开始就定义为 IDENTITY 的新列，然后删除旧列，并将新列重命名为旧名称。

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

马克

回答by greenoldman

There is cool solution described here: SQL SERVER – Add or Remove Identity Property on Column

这里描述了很酷的解决方案： SQL SERVER – Add or Remove Identity Property on Column

In short edit manually your table in SQL Manager, switch the identity, DO NOT SAVE changes, just show the script which will be created for the changes, copy it and use it later.

简而言之，在 SQL 管理器中手动编辑您的表，切换身份，不要保存更改，只需显示将为更改创建的脚本，复制并稍后使用。

It is huge time saver, because it (the script) contains all the foreign keys, indices, etc. related to the table you change. Writting this manually... God forbid.

它可以节省大量时间，因为它（脚本）包含与您更改的表相关的所有外键、索引等。手动写这个……上帝保佑。

回答by Oto Shavadze

Consider to use SEQUENCE instead of IDENTITY.

考虑使用SEQUENCE 而不是 IDENTITY。

IN sql server 2014 (I don't know about lower versions) you can do this simply, using sequence.

在 sql server 2014（我不知道较低版本）中，您可以使用序列简单地执行此操作。

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

From here: Sequence as default value for a column

从这里：序列作为列的默认值

回答by CSK

Simple explanation

简单说明

Rename the existing column using sp_RENAME

使用 sp_RENAME 重命名现有列

EXEC sp_RENAME 'Table_Name.Existing_ColumnName' , 'New_ColumnName', 'COLUMN'

EXEC sp_RENAME 'Table_Name.Existing_ColumnName', 'New_ColumnName', 'COLUMN'

Example for Rename :

重命名示例：

The existing column UserID is renamed as OldUserID

现有列 UserID 重命名为 OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

Then add a new column using alter query to set as primary key and identity value

然后使用更改查询添加一个新列以设置为主键和标识值

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Example for Set Primary key

设置主键示例

The new created column name is UserID

新创建的列名是 UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

then Drop the Renamed Column

然后删除重命名的列

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Example for Drop renamed column

删除重命名列的示例

ALTER TABLE Users DROP COLUMN OldUserID

Now we've adding a primarykey and identity to the existing column on the table.

现在我们已经向表中的现有列添加了主键和标识。

回答by James Drinkard

I'm a java developer that happened to get on a team without a DBA and one where as a developer, I can't get DBA rights. I was tasked with moving an entire schema between two databases, so without having a DBA, I had to do it and do it by running scripts, not being able to use the GUI in SQL Server 2008 because I didn't have admin privileges.

我是一名 Java 开发人员，碰巧加入了一个没有 DBA 的团队，而作为一名开发人员，我无法获得 DBA 权限。我的任务是在两个数据库之间移动整个架构，因此没有 DBA，我必须通过运行脚本来完成它，因为我没有管理员权限，因此无法在 SQL Server 2008 中使用 GUI。

Everything was moved without issue, however, when running a stored procedure on the new schema.table, I found I lost the identity field in a table. I double checked the script that created the table and it was there, however, SQL Server didn't get it when I ran the script. I was told later by a DBA that he had seen this same problem before.

一切都没有问题地移动，但是，在新的 schema.table 上运行存储过程时，我发现我丢失了表中的标识字段。我仔细检查了创建表的脚本，它在那里，但是，当我运行脚本时，SQL Server 没有得到它。后来一位 DBA 告诉我，他以前也遇到过同样的问题。

In any event, for SQL Server 2008, these are the steps I took to get this resolved and they worked, so I'm posting this here in the hopes it will be a help to someone. This is what I did as I had FK dependencies on another table that made this more difficult:

无论如何，对于 SQL Server 2008，这些是我为解决此问题而采取的步骤并且它们起作用了，因此我将其发布在这里希望对某人有所帮助。这就是我所做的，因为我对另一个表有 FK 依赖，这使得这变得更加困难：

I used this query to verify the identity was indeed missing and to view dependencies on the table.

我使用这个查询来验证身份确实丢失并查看表上的依赖关系。

1.) Find statistics on a table:

1.) 在表上查找统计信息：

exec sp_help 'dbo.table_name_old';

2.) Create a duplicate, identical new table, except add an identity field on the PK field where it had been before.

2.) 创建一个重复的、相同的新表，除了在之前的 PK 字段上添加一个标识字段。

3.) Disable the identity to move data.

3.) 禁用身份移动数据。

SET IDENTITY_INSERT dbo.table_name ON 

4.) Transfer the data.

4.) 传输数据。

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.) Verify the data is there.

5.) 验证数据是否存在。

SELECT * FROM dbo.table_name_new

6.) Re-enable the identity.

6.) 重新启用身份。

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) This is the best script I found to get all the FK relationships to verify which table(s) the original table references as dependencies and I came across many, so it is a keeper!

7.)这是我找到的最好的脚本，可以获取所有 FK 关系以验证原始表引用哪些表作为依赖项，并且我遇到了很多，所以它是一个守护者！

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.) Make sure you have all the PK and FK scripts for all the tables involved, before this next step.

8.) 在下一步之前，请确保您拥有所有涉及的表的所有 PK 和 FK 脚本。

9.) You can right-click on each key and script this using SQL Server 2008

9.) 您可以右键单击每个键并使用 SQL Server 2008 编写脚本

10.) Drop the FK(s) from the dependency table(s) using this syntax:

10.) 使用以下语法从依赖项表中删除 FK(s)：

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) Drop the original table:

11.) 删除原始表：

DROP TABLE dbo.table_name_old;

13.) These next steps rely on the scripts you created in SQL Server 2008 in step 9.

13.) 这些后续步骤依赖于您在第 9 步中在 SQL Server 2008 中创建的脚本。

--Add the PK to the new table.

--将PK添加到新表中。

--Add the FK to the new table.

--将 FK 添加到新表中。

--Add the FK's back to the dependency table.

-- 将 FK 重新添加到依赖项表中。

14.) Verify everything is correct and complete. I used the GUI to look at the tables.

14.) 验证一切是否正确且完整。我使用 GUI 来查看表格。

15.) Rename the new table to the original tables name.

15.) 将新表重命名为原始表名。

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

Finally, everything worked!

最后，一切顺利！

回答by Nagendra Upwanshi

As I understood in normal cases we are creating a table with Primary keywhich is having Identity property
So Renameor Deletea column which is associated with Primary Keyconstraintwill not be possible because constraint Rules are validating column structure.
Tto achieve this we have to process some steps in the following way:
Let us assume TableName = 'Employee'and ColumnName = 'EmployeeId'

1. Add new column 'EmployeeId_new' in the 'Employee' table
ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)

正如我所理解的，在正常情况下，我们正在创建一个具有标识属性的主键的表， 因此无法重命名或删除与主键约束关联的列，因为约束规则正在验证列结构。 为了实现这一点，我们必须按以下方式处理一些步骤： 让我们假设TableName = 'Employee'和ColumnName = 'EmployeeId'1. 在 'Employee' 表中添加新列 'EmployeeId_new' ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY( 1,1)

1. Now remove column 'EmployeeId' from 'Employee' table
   ALTER TABLE Employee DROP COLUMN EmployeeId

2. This will throw error because of Primary Key Constraint rules are applicable and validating column structure.
   *### 'Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].'###

3. So we have to remove the Primary Key constraint first from the table 'Employee' then we can remove the column
   ALTER TABLE Employee DROP constraint [PK_dbo.Employee]

4. Now we can remove the column 'EmployeeId' from 'Employee' table as did in the previous step where we got error
   ALTER TABLE Employee DROP COLUMN EmployeeId

5. Now Column 'EmployeeId' removed from table So we will Rename the newly added new column 'EmployeeId_new' with 'EmployeeId'
   sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'

6. To rearrange the table in the same form as It was, we have to add Primary Key Constraint for the column 'EmployeeId'
   ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)

2. 现在从 'Employee' 表中删除列 'EmployeeId'
   ALTER TABLE Employee DROP COLUMN EmployeeId

3. 这将引发错误，因为主键约束规则适用且正在验证列结构。
   *### '消息 5074，级别 16，状态 1，第 1 行对象 [PK_dbo.Employee] 依赖于列 [EmployeeId]。' ###

4. 所以我们必须首先从表 'Employee' 中删除主键约束，然后我们可以删除列
   ALTER TABLE Employee DROP 约束 [PK_dbo.Employee]

5. 现在我们可以从 'Employee' 表中删除列 'EmployeeId'，就像在上一步中我们得到错误
   ALTER TABLE Employee DROP COLUMN EmployeeId 一样

6. 现在从表中删除列 'EmployeeId' 所以我们将重命名新添加的新列 'EmployeeId_new' 与 'EmployeeId'
   sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'

7. 要以与以前相同的形式重新排列表，我们必须为列“EmployeeId”添加主键约束
   ALTER TABLE Employee 添加约束 [PK_dbo.Employee] 主键 (EmployeeId)

8. Now the table 'Employee' with 'EmployeeId' is modified for Identity rules along with existing primary key constraint

8.现在表 'Employee' 和 'EmployeeId' 被修改为 Identity 规则以及现有的主键约束

回答by SQLMenace

you can't do it like that, you need to add another column, drop the original column and rename the new column or or create a new table, copy the data in and drop the old table followed by renaming the new table to the old table

您不能这样做，您需要添加另一列，删除原始列并重命名新列或创建新表，将数据复制并删除旧表，然后将新表重命名为旧表桌子

if you use SSMS and set the identity property to ON in the designer here is what SQL Server does behind the scenes. So if you have a table named [user] this is what happens if you make UserID and identity

如果您使用 SSMS 并在设计器中将标识属性设置为 ON，则 SQL Server 在幕后所做的就是这样。因此，如果您有一个名为 [user] 的表，那么如果您将 UserID 和 identity 设为

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

Having said that there is a way to hack the system table to accomplish it by setting the bitwise value but that is not supported and I wouldn't do it

话虽如此，有一种方法可以通过设置按位值来破解系统表来完成它，但不支持这种方法，我不会这样做

回答by Raj

By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data.

按照设计，没有简单的方法可以打开或关闭现有列的标识功能。唯一干净的方法是创建一个新列并使其成为标识列或创建一个新表并迁移您的数据。

If we use SQL Server Management Studio to get rid of the identity value on column "id", a new temporary table is created, the data is moved to the temporary table, the old table is dropped and the new table is renamed.

如果我们使用 SQL Server Management Studio 去除“id”列上的标识值，则会创建一个新的临时表，将数据移动到临时表中，删除旧表并重命名新表。

Use Management Studio to make the change and then right click in the designer and select "Generate Change Script".

使用 Management Studio 进行更改，然后在设计器中右键单击并选择“生成更改脚本”。

You will see that this is what SQL server in doing in the background.

您将看到这是 SQL 服务器在后台执行的操作。