SQL Server add auto increment primary key to existing table
No - you have to do it the other way around: add it right from the get go as INT IDENTITY - it will be filled with identity values when you do this:
ALTER TABLE dbo.YourTable ADD ID INT IDENTITYand then you can make it the primary key:
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY(ID)or if you prefer to do all in one step:
ALTER TABLE dbo.YourTable ADD ID INT IDENTITY CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED
You can't "turn on" the IDENTITY: it's a table rebuild.
If you don't care about the number order, you'd add the column, NOT NULL, with IDENTITY in one go. 150k rows isn't a lot.
If you need to preserve some number order, then add the numbers accordingly. Then use the SSMS table designer to set the IDENTITY property. This allows you to generate a script which will do the column drop/add/keep numbers/reseed for you.
I had this issue, but couldn't use an identity column (for various reasons).I settled on this:
DECLARE @id INTSET @id = 0 UPDATE table SET @id = id = @id + 1 Borrowed from here.