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