question

mason avatar image
mason asked

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.

indexingdatatypessparse-columns
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - don't forget the 'remove all FKs that reference the PK' :)
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.