SQL Server：如何添加新的标识列并用 id 填充列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9414826/
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
SQL Server: how to add new identity column and populate column with ids?
提问by Sergejs
I have a table with huge amount of data. I'd like to add extra column idand use it as a primary key. What is the better way to fill this column with values from one 1to row count
我有一个包含大量数据的表。我想添加额外的列id并将其用作主键。用从 11到1 的值填充此列的更好方法是什么？row count
Currently I'm using cursor and updating rows one by one. It takes hours. Is there a way to do that quicker?
目前我正在使用游标并一一更新行。这需要几个小时。有没有办法更快地做到这一点？
Thank you
谢谢
回答by marc_s
Just do it like this:
只需这样做：
ALTER TABLE dbo.YourTable
ADD ID INT IDENTITY(1,1)
and the column will be created and automatically populated with the integervalues (as Aaron Bertrand points out in his comment - you don't have any control over which row gets what value - SQL Server handles that on its own and you cannot influence it. But all rows will get a valid intvalue - there won't be any NULLor duplicate values).
并且该列将被创建并自动填充integer值（正如 Aaron Bertrand 在他的评论中指出的那样 - 您无法控制哪一行获得什么值 - SQL Server 自行处理，您无法影响它。但是所有行都将获得有效值int- 不会有任何NULL值或重复值）。
Next, set it as primary key:
接下来，将其设置为主键：
ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable PRIMARY KEY(ID)
回答by Misa
If you want to add row numbers in a specific order you can do ROW_NUMBER() into a new table then drop the original one. However, depending on table size and other business constraints, you might not want to do that. This also implies that there is a logic according to which you will want the table sorted.
如果您想按特定顺序添加行号，您可以在新表中执行 ROW_NUMBER() 然后删除原始表。但是，根据表大小和其他业务限制，您可能不想这样做。这也意味着有一个逻辑，您希望根据该逻辑对表进行排序。
SELECT ROW_NUMBER() OVER (ORDER BY COL1, COL2, COL3, ETC.) AS ID, *
INTO NEW_TABLE
FROM ORIGINAL_TABLE
