SQL Server add auto increment primary key to existing table
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.
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 IDENTITY
and 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