Menu
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?
272
An identity column is an integer or bigint column whose values are automatically generated from a system-defined sequence. An identity column provides a way to automatically generate a unique numeric value for each row in a table.
You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.
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
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
