Adding identity specification back to a primary key column

Question

Adding identity specification back to a primary key column

j.a.c

SSCrazy

Points: 2283
More actions
November 2, 2009 at 12:02 pm

#216282

Hi,
I am in the situation where I have to add the identity property back on an existing primary key column on several tables in a production environment.
What would be the best way to do this in order to minimize downtime?
Should I follow what SSMS scripts out or is there a better way to add the identity specification?
Thanks,
Andy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

j.a.c (11/2/2009)
Hi,
I am in the situation where I have to add the identity property back on an existing primary key column on several tables in a production environment.
What would be the best way to do this in order to minimize downtime?
Should I follow what SSMS scripts out or is there a better way to add the identity specification?
Thanks,
Andy

There is a better way right click on the table go to design and change IDENTITY specification from no to yes run some test first, see the attached file.

Kind regards,
Gift Peddie

j.a.c SSCrazy Points: 2283 More actions · Answer 2

Hi,

You can see the script that SSMS generates when you update the Identity specification using the SSMS gui. It involves creating a new table, moving all data from the source table over to the new table, dropping the old table, renaming the new table to the correct value and rewiring up all the FKs.

In a really busy system you could be getting hundreds of requests per second to the table that you need to add the identity to. Is there a faster way to do this?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

j.a.c (11/3/2009)
Hi,
You can see the script that SSMS generates when you update the Identity specification using the SSMS gui. It involves creating a new table, moving all data from the source table over to the new table, dropping the old table, renaming the new table to the correct value and rewiring up all the FKs.
In a really busy system you could be getting hundreds of requests per second to the table that you need to add the identity to. Is there a faster way to do this?

The other option is to drop the column and recreate it with IDENTITY enabled that is the reason I told you to run some test. So ALTER table drop constraint, drop index and drop column, then add column SET IDENTITY, create index.

Kind regards,
Gift Peddie