CODEWITHSUNDEEP
sql-serversql-server-2008
My Other Me
My Other Me

Reputation: 5117

Converting int primary key to bigint in Sql Server

We have a production table with 770 million rows and change. We want(/need?) to change the Primary ID column from int to bigint to allow for future growth (and to avoid the sudden stop when the 32bit integer space is exhausted)

Experiments in DEV have shown that this is not as simple as altering the column as we would need to drop the index and then re-create it. So far in DEV (which is a bit humbler than PROD) the dropping of the index has not finished after 1 and a half hours. This table is hit 24/7 and having it offline for such a long time is not an option.

Has anyone else had to deal with a similar situation? How did you get it done?

Are there alternatives?

Edit: Additional Info:

Upvotes: 11

Views: 8355

Answers (3)

SQLGuyChuck
SQLGuyChuck

Reputation: 322

What I had to do was copy the data into a new table with the desired structure (primary/clustered key only, non-clustered/FK once complete). If you don't have the room, you could bcp out the data and back in. You may need an application outage to make this happen.

What doesn't work: alter table Orderhistory alter column ID bigint because of the primary key. Don't drop the key and alter column as you will just fill your log file and take much longer than copy/bcp.

Never use the SSMS tools designer to change a column property, it copies table into temp table then does a rename once done. Lookup the alter table alter column syntax and use it and possibly defrag once complete if you modified a column wider that sits in middle of table.

Upvotes: 0

Rich Hill
Rich Hill

Reputation: 463

You could attempt a staged approach.

  1. Create a new bigint column
  2. Create an insert trigger to keep new entries in sync with the 2 columns
  3. Execute an update to populate all the empty values in the bigint column with the converted value
  4. Change the primary index on the table from your old id column to the new one
  5. Point any FK's and queries to use the new column
  6. Change the new column to become your identity column and remove the insert trigger from #2
  7. Delete the old ID column

You should end up spreading the pain out over these 7 steps instead of hitting it all at once.

Upvotes: 11

Beth
Beth

Reputation: 9617

Create a parallel table with the longer data type for new rows and UNION the results?

Upvotes: 1

Related Questions