March 8, 2011 at 8:19 am
Here is my situation. I have a clustered primary key and several nonclustered indexes on a large table. I have to modify one of the columns in the PK, going to bigint from int. So what I am wondering is if this is the procedure that I need to follow in order to do things the best and fastest?
1. Remove the PK
2. Modify the column from int to bigint
3. Readd the PK
4. Reindex all the remaining nonclustered indexes
Thx again...
March 8, 2011 at 8:24 am
No.
Modifying the clustered index will rebuild all nonclustered indexes. The procedure you have there will rebuild all the nonclustered indexes 3 times. Far from fast.
Drop all nonclustered indexes first. Then make the necessary changes to the clustered index (in this case the pk), then recreate all the nonclustered indexes. That way you're only rebuilding them once.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2011 at 8:30 am
Thx. Out of curiosity, can I ask why they get rebuilt 3X? I assume one is when readd the PK it will rebuild them and then in my last step I had rebuild them. But that's just two. Where's the third?
March 8, 2011 at 8:39 am
I believe the first will be when you remove the PK. Since it's a clustered index, the non-clustered indexes use the PK as the unique identifier. By removing the PK, the non-clustered indexes must be rebuild using a rowID instead of the PK.
March 8, 2011 at 8:56 am
be careful how you remove your PK. If you script it with a drop, dropping a clustered index will delete the table.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 8, 2011 at 9:13 am
I think you'd be better copying the data into a new table, with the changed data type, then building nonclustered indexes, then renaming the old, and rename the new.
If you have constraints, you should be able to script them out and run a disable,enable on this before/after the copy.
March 8, 2011 at 9:14 am
Excellent idea....and safer. 🙂
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 8, 2011 at 10:20 am
Also have you considered the fact that changing of data type on a primary key wont be allowed unless the corresponding foreign key data type also is not of the same type.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 8, 2011 at 10:36 am
I just dropped a clustered PK on a table (without nonclustereds) last week and it did not drop the whole table. Are you sure about this? I guess I can test it, but I'm almost positive that's what I did...
March 8, 2011 at 10:46 am
Whisper9999 (3/8/2011)
Thx. Out of curiosity, can I ask why they get rebuilt 3X? I assume one is when readd the PK it will rebuild them and then in my last step I had rebuild them. But that's just two. Where's the third?
First after removing the PK (step 1)
Second after adding it back (step 3)
Third when explicitly reindexed (step 4)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 8, 2011 at 10:47 am
ChazMan (3/8/2011)
be careful how you remove your PK. If you script it with a drop, dropping a clustered index will delete the table.
Sorry, but this is incorrect. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 8, 2011 at 10:51 am
No I am not speaking about indexes.
I am saying that if you try to change the data type of a primary key (as you mentioned earlier) then it will fail if the corresponding foreign key data type is different.
So basically you will have to drop the relationship,change the data type of the primary key(in your case to bigint),change the data type of the foreign key (if any to bigint) and then recreate the relationship.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply