SQLSERVER: How to alter an existing table int primary key to become an identity column?

Question

My database has a table with thousands of records. The primary key is an integer. There's a lot of foreign key constraints associated with this column.

I want to change this column to become an identity key. What's the best way to do it? I also need to send this update to our clients installations.

Bonus points for an answer that works in Sql Server 2000.

Answer 1

In Enterprise Manager, right click the table in table view, select design.

click the left hand side of the column (then, double click identity, in columns underneath, in column properties, turns it on, defaults to auto increment 1

Answer 2

If the column is an integer as a part of existing relationships then it is already unique. Therefore you do not have to worry about duplicates! That's one huge hassle avoided already.

You can either issue an ALTER TABLE command to change the column or you can do it with Enterprise Manager.

Answer 3

There is no single "ALTER TABLE" DDL for changing an existing column to an identity column. You can only add a new identity column to an existing table.

This can be done in Enterprise Manager but you need to be aware that Sql server is creating a new table and copying you data across in the background. You may have some issues with this. Here is an article that explains this a bit more http://www.mssqltips.com/tip.asp?tip=1397

In your scenario i think you will need a combination of this script and something to disable and reenable your fk's.

Answer 4

I'm still investigating if it will work, but there's a great feature in SQL Server Management Studio that may save my day.

In SSMS go to Options -> Designers -> Table and Database Designers, check "Auto generate change scripts" and uncheck "Prevent saving changes that require table re-creation".

In object explorer, go to you table and select the column that will get the Identity specification. Right click and select modify. In the Column properties panel, expand the tree "Identity Specification" and change "(Is Identity)" to yes. Now on the upper left size, select the icon "Generate script". Pay attention to the warning messages.

Now you will have a generated script that will drop all your constrainsts, recreate the table with identity, and recreate the constraints. WOW!

I'll test it and post here my results.

Update: Everything worked fine. I forgot to say in the question that I need the script to reproduce the modification in our clients instalations.

This site is really fantastic. The time I spent to write my own answer I got 3 others!