给现有列添加身份标识

Question

给现有列添加身份标识

sqlsql-serveralter-tableidentity-column

560

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

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

如何使用SQL命令更改该列以具有标识属性？

- Kirschstein

21个回答

255

在SQL 2005及以上版本中，有一个技巧可以解决这个问题，而无需更改表的数据页。这对于大型表格非常重要，因为触及每个数据页可能需要几分钟或几小时的时间。即使标识列是主键、聚集索引或非聚集索引的一部分，或者存在其他容易导致简单的“添加/删除/重命名列”解决方案出现问题的情况，这个技巧也同样适用。

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

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

我用这个技巧将一个25亿行表的列从IDENTITY转换为非IDENTITY（以便运行查询计划对非IDENTITY列更有效的多小时查询），并在不到5秒钟的时间内恢复了IDENTITY设置。

以下是它的代码示例。

 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;

这显然比其他答案中的解决方案更复杂，但如果你的表很大，这可以成为一个真正的救命稻草。有一些注意事项：

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

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

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

这里还有一个值得一提的注意事项。虽然新表可以从旧表中接收数据，所有新行将按照标识模式插入，但它们将从1开始，并且如果该列是主键，则可能会出现问题。切换后立即运行DBCC CHECKIDENT('<newTableName>')。有关更多信息，请参见msdn.microsoft.com/en-us/library/ms176057.aspx。

如果正在积极地向表中添加新行（这意味着在添加IDENTITY和新行之间几乎没有停机时间），则需要手动设置新表模式中的标识种子值大于表中最大的现有ID，例如IDENTITY (2435457, 1)，而不是使用DBCC CHECKIDENT。你可能能够在事务中包含ALTER TABLE...SWITCH和DBCC CHECKIDENT（或者不包含-尚未测试过），但手动设置种子值似乎更容易和更安全。

显然，如果不再向表中添加新行（或仅偶尔添加，如每日ETL过程），则不会发生竞争条件，因此DBCC CHECKIDENT就可以了。

- Justin Grant

5

如果我的记忆没有错的话，我是从这篇文章中获取到这个想法的：http://www.sqlservercentral.com/articles/T-SQL/61979/。 - Justin Grant

2

顺便提一下，这似乎也适用于 SQL 2008 R2 标准版。也许他们启用了这个功能，就像他们现在启用了打开备份压缩的能力一样。 - Mason G. Zhwiti

3

@jbatista - OP 的问题说明他已经在表上有一个主键，并且已经能够确保正确的值，但他只是想将其更改为 IDENTITY 列。我上面的答案专注于这个狭窄的用例：如何将 IDENTITY 添加到列而不实际更改任何数据。我记录的方法对于大型表格来说是一个巨大的时间节约者。如果您需要更改数据，则需要使用其他解决方案。 - Justin Grant

3

这里还有一个值得一提的注意事项。虽然新表可以愉快地从旧表接收数据，并且所有新行都将按照标识模式插入，但它们将从1开始并且可能会破坏主键列。在切换后立即考虑运行 DBCC CHECKIDENT('<newTableName>')。请参阅 https://msdn.microsoft.com/en-us/library/ms176057.aspx 获取更多信息。 - Eric Wu

3

这是一个很好的回答！另外请注意，列的可空性必须相同。因此，如果您需要更改列的可空性，则必须在稍后的步骤中执行。主键约束也是如此。我还将表创建中的标识值更改为与当前最大值匹配：IDENTITY（maxID + 1，1）。 - Philippe

显示剩余9条评论

108

你不能将一个列更改为 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_s

参数 @objname 不明确，或者声明的 @objtype (COLUMN) 错误。 - Krisztián Balla

1

@JennyO'Reilly: 把它放到一个单独的问题中，并展示给我们你正在使用的完整命令！ - marc_s

2

失败的是sp_rename过程。我在stackoverflow上通过搜索错误文本找到了解决方案。似乎是由于括号的严格语法规则，尽管我的表名中没有任何特殊字符。 - Krisztián Balla

1

或者可以这样写：'ALTER TABLE (yourTable) DROP COLUMN OldColumnName' 和 'ALTER TABLE (yourTable) ADD OldColumnName INT IDENTITY(1,1)'，为什么要重命名呢 :p - R K Sharma

Marc，我在一个巨大的表格（约3亿行）上尝试了这个确切的命令，但是在大约10分钟后我停止了这个过程。 - Naomi

18

考虑使用SEQUENCE而不是IDENTITY。

在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

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

- Oto Shavadze

也适用于我的SQL Server 2012。 - knb

17

这里有一个很不错的解决方案: SQL SERVER – Add or Remove Identity Property on Column

简而言之，在SQL Manager中手动编辑您的表，切换标识，不要保存更改，只需显示将创建用于更改的脚本，复制并稍后使用它。

这是一个巨大的时间节省器，因为（脚本）包含所有外键、索引等与更改表相关的内容。手动编写这些...天佑我们。

- greenoldman

这是我使用的解决方法--SSMS生成了T-SQL以进行更改...它通过创建一个新的与原表结构相同的临时表，然后将所有行复制到其中，删除原始表并更名来完成更改。可能需要一些时间才能完全运行，但它完美地工作了。 - mdelvecchio

我认为Pinal Dave实际上并没有说你需要运行生成的脚本，它只是展示了通过UI进行更改的效果... - Zack

SSMS中的脚本编写工具（用于更改表定义）实际上是唯一正确的工具，用于记录分区表。最合适的位置“任务”->“脚本表”总是忘记编写分区函数！ - Martijn van der Jagt

2

可能对某些人有帮助。在进行更改后获取更改脚本。在SSMS的设计模式下右键单击表格，选择“生成更改脚本”选项，并将脚本保存在本地驱动器中。 - Vijai

7

简单说明

使用 sp_RENAME 重命名现有列

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

重命名示例：

将现有列 UserID 重命名为 OldUserID

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

然后使用alter查询添加一个新列，将其设置为主键和自增值

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

设置主键的示例

新创建的列名为UserID。

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

然后删除已重命名的列

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

删除重命名列的示例

ALTER TABLE Users DROP COLUMN OldUserID

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

- Sathish Chelladurai

5

我是一名Java开发人员，恰好加入了一个没有DBA的团队，并且作为开发人员，我无法获得DBA权限。我被指派将整个模式移动到两个数据库之间，因此在没有DBA的情况下，我必须通过运行脚本来完成此任务，而不能使用SQL Server 2008中的GUI，因为我没有管理员特权。

所有内容都已成功移动，但是，在新的schema.table上运行存储过程时，我发现表中的标识字段丢失了。我仔细检查了创建表的脚本，发现标识字段确实存在，但是当我运行脚本时，SQL Server并没有将其获取。后来，一个DBA告诉我他曾经遇到过这个问题。

无论如何，对于SQL Server 2008，这些是我采取的步骤来解决这个问题，并且它们起作用了，所以我在这里发布希望能对某些人有所帮助。由于我在另一个表上具有FK依赖关系，因此我执行了以下操作：

我使用此查询验证标识是否确实丢失，并查看表上的依赖项。

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

exec sp_help 'dbo.table_name_old';

2.) 创建一个完全相同的新表，但在原有主键字段上添加一个自增字段。

3.) 禁用自增字段以移动数据。

SET IDENTITY_INSERT dbo.table_name ON

4.) 传输数据。

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

5.) 确认数据已存在。

SELECT * FROM dbo.table_name_new

6.) 重新启用身份验证。

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) 我发现这是获取所有外键关系的最佳脚本，以便验证原始表作为依赖项引用哪些表。我发现了许多脚本，但这个是一个不错的选择！

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.) 在进行下一步之前，请确保您拥有所有涉及表的PK和FK脚本。

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

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

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

11.) 删除原始表：

DROP TABLE dbo.table_name_old;

13.) 下一步的操作需要你在第9步中创建的SQL Server 2008中的脚本。

--向新表添加PK（主键）。

--向新表添加FK（外键）。

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

14.) 确认所有内容都正确无误。我使用GUI查看了表格。

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

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

最终，一切都已经正常运转！

- James Drinkard

5

正常情况下，我们创建一个带有“主键”的表，它具有“自增”功能。因此，重命名或删除与“主键约束”相关联的列是不可能的，因为约束规则正在验证列结构。为了实现这一点，我们需要按照以下步骤进行处理：假设TableName='Employee'，ColumnName='EmployeeId'

1. 在“Employee”表中添加新列“EmployeeId_new” ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)

2. 现在从“Employee”表中删除列“EmployeeId”。这将导致错误，因为主键约束规则适用并验证列结构。 ALTER TABLE Employee DROP COLUMN EmployeeId *### 'Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].' ###*

3. 所以我们必须先从表“Employee”中删除主键约束，然后才能删除该列。 ALTER TABLE Employee DROP constraint [PK_dbo.Employee]

4. 现在我们可以像前面的步骤一样删除“Employee”表中的“EmployeeId”列，其中我们遇到了错误。 ALTER TABLE Employee DROP COLUMN EmployeeId

5. 现在从表格中删除了“EmployeeId”列，所以我们将新添加的“EmployeeId_new”列重命名为“EmployeeId”。 sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'

6. 为列“EmployeeId”添加主键约束，以使表格与修改前相同。 ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)

7. 现在，“Employee”表具有符合自增规则的“EmployeeId”，同时还保留了现有的主键约束。 8. 完成。

- Nagendra Upwanshi

4

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

如果在SSMS中使用设计器将标识属性设置为ON，这是SQL Server在幕后执行的操作。因此，如果您有一个名为[user]的表，并将UserID设为标识，则会发生以下情况。

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

话虽如此，有一种方法可以通过设置位值来黑掉系统表来实现，但这是不受支持的，我不会这样做。

- SQLMenace

4

如果您正在使用Visual Studio 2017+，请按照以下步骤进行操作：

1. 在“服务器对象资源管理器”中右键单击您的表并选择“查看代码”。 2. 向您的列添加修饰符“IDENTITY”。 3. 更新。

这样做将为您完成所有操作。

- jdisla

是的！感谢您的建议！我在我的Windows 7计算机上没有安装SSMS版本，它无法对我的生产服务器上的表进行设计更改，因为它是2017年，我的SSMS是2014年的版本，而2017年的SSMS需要Windows 10。您让我的一天感到愉快。进入VS 2017>服务器资源管理器>连接到生产SQL Server>右键单击表>"打开表定义">完成！ - JustJohn

实际上，我发现您可以右键单击该字段，选择属性，然后在那里进行身份验证并选择是或否。 - JustJohn

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

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

- John Sansom · Accepted Answer

无法更改已有列的标识。

你有两个选项，

创建一个带有标识的新表并删除现有表
创建一个带有标识的新列并删除现有列

方法1（新表）：在这里，你可以在新创建的标识列上保留现有的数据值。请注意，如果未满足“if not exists”的条件，你将失去所有数据，因此请确保在删除时也加入该条件！

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'

方法二 (新增一列) 你无法保留新建标识列上原有的数据值，标识列将会保存数字序列。

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'

详见下面的微软SQL Server论坛帖子：

如何将列更改为identity(1,1)