int to bigint change

We have a table with 2 billion rows with an identity column defined as INT. I want to change it to BigINT. Is it faster to create a new table and insert all rows into new table or to Alter existing table and change the data type. If i do the second option, I also need to recreate the primary key that is on the identity column.

asked May 28 '10 at 01:31 PM in Default

mason
3 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

Based on the size of the tabel, I would

create a new tabel with the correct Clustered Index
Insert all rows from your table
rename the old table to TableOLD, and rename/remove constraints
create the constraints on the new tabel and nonclustered indexes
rename the new table
(after a few days) drop the old table

on one condition; that you have enough space in your database.

By doing this, you will avoid Page Splits.
Do the insert in chunks, or your tx log will fill up, when you do a backup or other stuff on the database.

You may also be able to avoid extensive down-time, as the insert may be able to run in parallel with your normal production work.

answered May 31 '10 at 07:20 AM

Henrik Staun Poulsen
549 ● 13 ● 14 ● 15

(comments are locked)

Based on the size, I would alter the existing table. It's going to take several steps though; create the column, migrate the data, change the PK, then remove the old column. Also, you my need to break this down into chunks to avoid filling your transaction log.

answered May 28 '10 at 02:01 PM