如何将新标识列添加到SQL Server中的表？

I am using SQL Server 2008 Enterprise. I want to add an identity column (as unique clustered index and primary key) to an existing table. Integer based auto-increasing by 1 identity column is ok. Any solutions?

我正在使用SQL Server 2008 Enterprise。我想将一个标识列（作为唯一聚簇索引和主键）添加到现有表。基于整数的自动增加1个标识列是可以的。有解决方案？

BTW: my most confusion is for existing rows, how to automatically fill-in new identity column data?

BTW：我最困惑的是现有行，如何自动填写新的标识列数据？

thanks in advance, George

乔治，提前谢谢

2 个解决方案

#1

you can use -

您可以使用 -

alter table <mytable> add ident INT IDENTITY

This adds ident column to your table and adds data starting from 1 and incrementing by 1.

这会将ident列添加到表中，并从1开始添加数据并递增1。

To add clustered index -

要添加聚集索引 -

CREATE CLUSTERED INDEX <indexName> on <mytable>(ident)

#2

have 1 approach in mind, but not sure whether it is feasible at your end or not. But let me assure you, this is a very effective approach. You can create a table having an identity column and insert your entire data in that table. And from there on handling any duplicate data is a child's play. There are two ways of adding an identity column to a table with existing data:

有一种方法，但不确定它是否可行。但请允许我向您保证，这是一种非常有效的方法。您可以创建具有标识列的表，并将整个数据插入该表中。从那里处理任何重复数据是孩子的游戏。有两种方法可以将标识列添加到包含现有数据的表中：

Create a new table with identity, copy data to this new table then drop the existing table followed by renaming the temp table.

Create a new column with identity & drop the existing column

For reference the I have found 2 articles : http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/ http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/

作为参考，我找到了2篇文章：http：//blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/http：//cavemansblog.wordpress .COM / 2009/04/02 / SQL-如何对附加的身份列到一个表，用数据/

#1