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.
| 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 relationshipsHow 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 |
 |
|
|
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. |
|
|
|
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. |
|
|
|
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? |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
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. |
|
|
|
|
|
|
|
|
Remove Primary Key from ID column