Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Remove Primary Key from ID column

Author  Topic 

audiudew
Starting Member

5 Posts

Posted - 2014-10-31 : 08:54:40
I have a table which has an Identity field and is INT data type. The max value in the column is 2,143,352,421 and so nightly jobs are crashing.

I need to change it to BigInt. However, this particular column is a primary key, with associated foreign keys. It also has indexes associated.

My process to convert this data type to BigInt is:

- Un-associate the Primary Key.
- Alter Table, Alter Column to change data type to BigInt.
- Re-associate the primary key relationships

How do I undo the primary key, so I maintain the records in the table?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-31 : 09:19:59
You can drop the primary key, change the column type to bigint, then re-establish the PK
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-31 : 09:53:15
If your the INT column has IDENTITY property, it is not going to be trivial. Create a table with identical structure, except for identity column being bigint, copy the data, drop the constraints on the original table, drop the original table, rename the new table to the original name, and then add the constraints back. You probably want to restrict access by putting the database into single user mode so the data does not get modified when you are doing this. I haven't tried this, it's all theory, so do it in a dev environment to make sure you are doing it right.

You can probably do this from the GUI as well (table designer), but the impact is going to be something similar - i.e., SQL Server is going to drop and recreate the table behind the scenes.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-31 : 15:15:51
Agreed, not trivial. I, too, would use a second table, assuming you have space for it. You'll very likely need to copy the rows in batches. If in FULL recovery model, you'll probably want to do extra log backups to keep the log from getting too full.

If you are familiar with Change Tracking -- or are willing to learn it -- after the copy, you can leave the main table active up until the last step. I'd create all non-clustered indexes on the second table so that everything is fully loaded to go back live when you're ready to make the second table the main table.

Then, once all rows are copied, rename the main table, name your second table to the main table name, re-apply any row mods as identified by change tracking from the original table back to your table, then make the table available again.
Go to Top of Page

audiudew
Starting Member

5 Posts

Posted - 2014-10-31 : 16:40:15
If I use a second table (with so many records, that might be tricky), do I need to drop FK relationship and Indexes on the linked tables?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-31 : 16:53:37
You can buy yourself some time if you reseed the identity value to the minimum negative number (-2147483648). But you'll need to be careful as it approaches back to 1.

And yes you need to drop FKs, etc for the second table approach. Yes it's going to be tricky. Agreed that the resolution is not trivial, but it is what it is.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

audiudew
Starting Member

5 Posts

Posted - 2014-10-31 : 17:00:55
I wish I had the liberty to reseed the identity values, but that would have severe consequences :/
Thanks for everyones responses.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-11-03 : 09:36:42
Just before the final cut-over from the new table to the old one you will need to drop FKs, then reinstate them immediately after the cut-over.
Go to Top of Page
   

- Advertisement -