Replace identity column from int to bigint

Well, it won't be a quick'n'easy way to do this, really....

My approach would be this:

1. create a new table with identical structure - except for the ID column being BIGINT IDENTITY instead of INT IDENTITY

   ----[ put your server into exclusive single-user mode here; user cannot use your server from this point on ]----

2. find and disable all foreign key constraints referencing your table

3. turn SET IDENTITY_INSERT (your new table) ON

4. insert the rows from your old table into the new table

5. turn SET IDENTITY_INSERT (your new table) OFF

6. delete your old table

7. rename your new table to the old table name

8. update all table that have a FK reference to your table to use BIGINT instead of INT (that should be doable with a simple ALTER TABLE ..... ALTER COLUMN FKID BIGINT)

9. re-create all foreign key relationships again

10. now you can return your server to normal multi-user usage again

What am I missing?

Why can't you just do this:

ALTER TABLE tableName ALTER COLUMN ID bigint

I guess try it in a test environment first but this always works for me

Probably the best way is to create a new table with a BIGINT IDENTITY column, move the existing data using SET IDENTITY_INSERT ON; and then rename the tables. You will need to do this during a maintenance window, just as you would if you changed the data type in Management Studio (which would similarly create a new table, move the data, and block everyone in the process).