Setting Primary Key auto increment with Alter Command

Question

Possible Duplicate:
How do I add auto_increment to a column in SQL Server 2008

Hey all i created the table in gui however i forgot to set the primary key to autoincrement! name of the table is Emp_CV.

Something like this:

alter table Emp_CV alter column Applicant_ID NOT NULL int AUTO_INCREMENT

how can i accomplish this? Am using SQL Server 2008!

no it does not allow the table to be altered once created! i have to change it with alter command! — Plotter, Aug 02 '12 at 22:39
i did not save the create query! i just want to know the right alter command to accomplish the above mentioned my assumed sql command — Plotter, Aug 02 '12 at 22:43
@burhan: that is the post once your creating a table not when you have already created it! — Plotter, Aug 02 '12 at 22:44

Nicholas Carey · Answer 1 · 2012-08-02T23:28:07.150

The short answer is...you can't. Here's what you have to do:

Ensure nobody/nothing is using your table.
Add a new integer column that will be the new identity column. It must allow nulls and must have the appropriate identity value.
execute set identity_insert dbo.foo on -- must specify your table name.
Seed it with the values of the existing identity column:

update dbo.foo set new_id = id
Drop any keys/constraints in which the existing identity column participates.
execute set identity_insert dbo.foo off
Drop the existing column.
Alter the new column, changing its nullity to not null.
Execute the command dbcc checkident( {your-table-name-here} , reseed ).
Execute sp_rename and give the new column the same name as the old column.
Recreate the keys/constraints dropped in step #4.
Might want to run sp_recompile on any stored procedures referencing this table.

Easy!

1 Answers1