Change huge table PK column data type

Question

Now that we've ran out of int capacity on a PK column (which is an IDENTITY) I'd like to do this to bigint, but simple ALTER TABLE seems to be unable to handle that big of a table. So my question is: how do I change the type of a PK column with keeping actual values in place and do I need to alter referencing tables as well?

Answer 1

I'm think you can only create a new database with changed PK datatype, and then export/import data, or bulk insert into new, then rename new database. Of course this actual if you have many referenced tables and your new PK datatype not compatible with previous.

Answer 2

What we would do is:

save your table

1. create a new table with the correct structure
2. disable all constraints on these tables, and the ones that reference them
3. move all data into the new table, with alteration of the field ; it can be done by batches
4. delete the old table when it's empty
5. rename the new table to the old name

6. enable all constraints on all tables (some FK column and constraints probably need fixing too... But they are not PK, so they are modifiable)

   6 edited (thanks to Alexey)

This is clean, doable in batches, well understood.

Answer 3

In addition to KLE's suggestion, the following queries might help:

To disable all constraints on the tables that reference oldTable try to execute the output of the following query:

SELECT 'ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' NOCHECK CONSTRAINT ' + fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns AS fkc ON fk.OBJECT_ID = fkc.constraint_object_id
WHERE OBJECT_NAME (fk.referenced_object_id) = 'oldTable'

To move all data into the new table, with alteration of the field try this:

INSERT INTO newTable
SELECT CONVERT(BIGINT, ID) AS ID, COL1, COL2, ..., COLN
FROM oldTable

To drop the old table:

DROP TABLE oldTable

To rename the new table to the old name:

sp_rename newTable, oldTable

To reenable all the constraints on the tables that reference oldTable, try to execute the output of the following query:

SELECT 'ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' CHECK CONSTRAINT ' + fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns AS fkc ON fk.OBJECT_ID = fkc.constraint_object_id
WHERE OBJECT_NAME (fk.referenced_object_id) = 'oldTable'

Hope it helps...

Answer 4

You will need to also alter the child tables. After all you will now be trying to insert a big int into them as well. I would change over the child tables first

This is not an easy or short process. I would suggest to you that you tell your users the the database is going to be down for maintenance (you can gauge how long by how long it takes to do dev) on a set date and reset the databse to single user mode while you make these changes. You don't want to lose data that is added (or changed)by users to one table while you are switching to the other one. if for somereason you can't havea maintence window (and I strongly suggest for the sake of data integrity that you do), then you must change the child tables over first to avoid insert errors if your a really close to the limit and will be seeing the large numbers almost immediately.

Make sure to script the entire datbase structure including defaults, triggers, check constrants indexes etc as you will want to recreate everything.

Make sure to do all this through scripts on dev. That will make it much easier to do one prod once you have tested the process out.