Replace identity column from int to bigint
sql, sql-server, sql-server-2008
Solution
Well, it won't be a quick'n'easy way to do this, really....
My approach would be this:
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 ]----
find and disable all foreign key constraints referencing your table
turn `SET IDENTITY_INSERT (your new table) ON`
insert the rows from your old table into the new table
turn `SET IDENTITY_INSERT (your new table) OFF`
delete your old table
rename your new table to the old table name
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`)
re-create all foreign key relationships again
now you can return your server to normal multi-user usage again
Problem
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 `int`. I want to upgrade that column to be `bigint`. 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?