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

by

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)

Leave a Comment