Matan L Matan L - 4 months ago 8
SQL Question

Replace identity column from int to bigint

I am using SQL Server 2008, and I have a table that contains about 50 mill rows.

That table contains a primary identity column of type


I want to upgrade that column to be

I need to know how to do that in a quick way that will not make my DB server unavailable,
and will not delete or ruin any of my data

How should I best do it ? what are the consequences of doing that?


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