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.

Solution 1

There's a great feature in SQL Server Management Studio that saved 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 your 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 constraints, 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 installations.

Solution 2

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

Solution 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.

Comments

[Stuart]

don't you think my suggestion was simpler? :)

[ZygD]

@STuart: It's the same solution but neves saves his script first.

[ZygD]

@Neves: Enterprise Manager has the same conecpt of "change and save"

[Stuart]

@gbn - I'm aware of that, thanks for stating the obvious :) just trying to keep things simple, why generate scripts you don't need

[komalV]

@neves: thanks for sharing, I also need to do this on multiple environments and needed the script.