在SQL 2005及以上版本中，有一个技巧可以在不改变表的数据页的情况下解决这个问题。这对于大型表很重要，因为在这些表中，处理每个数据页可能需要花费几分钟或几小时的时间。即使标识列是一个主键，是聚集或非聚集索引的一部分，或者其他可能阻碍更简单的“添加/删除/重命名列”解决方案的陷阱，这个技巧也同样有效。

这里有一个技巧:你可以使用SQL Server的ALTER TABLE…SWITCH语句在不改变数据的情况下更改表的模式，这意味着您可以使用相同的表模式替换IDENTITY表，但没有IDENTITY列。同样的技巧也适用于向现有列添加IDENTITY。

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

我使用这个技巧在不到5秒的时间内将一个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; 

这显然比其他答案更复杂，但如果您的表很大，这可能是一个真正的救星。这里有一些注意事项:

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.

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

更新- Eric Wu在下面评论了关于这个解决方案的重要信息。复制在这里，以确保它得到更多的关注:

这里还有一个值得一提的警告。虽然 新表将愉快地从旧表接收数据，并且所有的 新行将按照标识模式插入 从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 CHECKIDENT you'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...SWITCH and the DBCC CHECKIDENT in a transaction (or not-- haven't tested this) but seems like setting the seed value manually will be easier and safer.

显然，如果没有新行被添加到表中(或者它们只是偶尔添加，比如每天的ETL进程)，那么这种竞争条件就不会发生，因此DBCC CHECKIDENT是可以的。

简单的解释

使用sp_RENAME重命名现有列

EXEC sp_RENAME 'Table_Name。Existing_ColumnName'， 'New_ColumnName'， 'COLUMN'

重命名示例:

现有列UserID被重命名为OldUserID

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

然后使用alter query添加一个新列来设置为主键和标识值

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

现在，我们向表上的现有列添加了一个主键和标识。

在SQL 2005及以上版本中，有一个技巧可以在不改变表的数据页的情况下解决这个问题。这对于大型表很重要，因为在这些表中，处理每个数据页可能需要花费几分钟或几小时的时间。即使标识列是一个主键，是聚集或非聚集索引的一部分，或者其他可能阻碍更简单的“添加/删除/重命名列”解决方案的陷阱，这个技巧也同样有效。

这里有一个技巧:你可以使用SQL Server的ALTER TABLE…SWITCH语句在不改变数据的情况下更改表的模式，这意味着您可以使用相同的表模式替换IDENTITY表，但没有IDENTITY列。同样的技巧也适用于向现有列添加IDENTITY。

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

我使用这个技巧在不到5秒的时间内将一个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; 

这显然比其他答案更复杂，但如果您的表很大，这可能是一个真正的救星。这里有一些注意事项:

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.

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

更新- Eric Wu在下面评论了关于这个解决方案的重要信息。复制在这里，以确保它得到更多的关注:

这里还有一个值得一提的警告。虽然 新表将愉快地从旧表接收数据，并且所有的 新行将按照标识模式插入 从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 CHECKIDENT you'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...SWITCH and the DBCC CHECKIDENT in a transaction (or not-- haven't tested this) but seems like setting the seed value manually will be easier and safer.

显然，如果没有新行被添加到表中(或者它们只是偶尔添加，比如每天的ETL进程)，那么这种竞争条件就不会发生，因此DBCC CHECKIDENT是可以的。

这里有一个很酷的解决方案: 在列上添加或删除标识属性

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

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

根据我目前的情况，我采用这种方法。我想通过脚本插入数据后给一个主表的身份。

因为我想要追加身份，所以它总是从1开始到我想要的记录计数的结束。

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

这将创建具有identity的相同主键列

我使用了这个链接:https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

向现有表添加主键

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

如果你使用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

已经说过，有一种方法可以通过设置位值来破解系统表，但这是不支持的，我不会这么做