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.

more ▼

asked May 28, 2010 at 01:31 PM in Default

mason gravatar image

3 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 31, 2010 at 07:20 AM

Henrik Staun Poulsen gravatar image

Henrik Staun Poulsen
579 13 15 16

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 28, 2010 at 02:01 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1 - don't forget the 'remove all FKs that reference the PK' :)
May 29, 2010 at 06:37 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 28, 2010 at 01:31 PM

Seen: 2536 times

Last Updated: May 29, 2010 at 09:09 AM