May 29, 2008 at 9:29 pm
Hi,
I have a huge table, let me call it as table1, with 100s of 1000s of records with one primary key. I want to increase the PK column width. I thought I can follow these steps in the management studio:
1. Drop the PK constraint
2. Alter the PK column width
3. Add the PK constraint again
When I did step 1, the server took a long time and didn't respond and so I had to cancel that sql. But, am I supposed to drop the foreign key from the other tables that use this pk as a fk ? Do I have to create a new column in this table1 with increased width and copy all the data from the pk column to the new column and then drop the pk and delete the column then rename the new column to pk name and make it the pk? What is the correct way to do it?? I am not a DBA and try to perform some DBA activities on need basis. Any help would be appreciated. Thanks a lot.
May 30, 2008 at 3:27 am
Consider creating a new table and copying data from the old table in batches (e.g. 1000 rows at a time) or use SSIS to copy data to the new table.
After the data is copied you can drop the old table and rename the new one. Of course, before you drop the table you must take remove the references to it.
Two more things:
1) Backup the database before the operation;
2) Migrate the data when the database is not accessed ba users (e.g. put it in single user mode).
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 30, 2008 at 5:50 am
You're pretty close
But, am I supposed to drop the foreign key from the other tables that use this pk as a fk ?
Yes, you need to drop the foreign key constraints before you drop the primary key.
You can do it your way or you can follow the post above. Either way will work. Since you're expanding the size of the column, your way is probably a tad faster. You will have to script out all the primary & foreign key constraints so that they can be dropped and recreated.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 30, 2008 at 9:06 am
If you are going to use the "copy" to new table method I would use "SELECT INTO..." instead of batches of inserts.
ALL these activities will require heavy I/O anyway. You should plan to do this at off hours.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply