SQL Server: how to add new identity column and pop

问题:

I have a table with huge amount of data. I'd like to add extra column id and use it as a primary key. What is the better way to fill this column with values from one 1 to 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

回答1:

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 integer values (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 int value - there won't be any NULL or duplicate values).

Next, set it as primary key:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable PRIMARY KEY(ID)

回答2:

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.

SELECT ROW_NUMBER() OVER (ORDER BY COL1, COL2, COL3, ETC.) AS ID, *
INTO NEW_TABLE
FROM ORIGINAL_TABLE