SQL Server: how to add new identity column and populate column with ids?

Solution 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)

Solution 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