July 1, 2009 at 4:39 am
im looking for a simple article explaining how to move tables between filegroups, and the caveats of the methods.
For instance, I thought i could simply alter the table, specifying a new filegroup, but i see other folks saying if i recreate the clustered index on the 2nd filegroup, the table is automatically moved.
is the table indeed moved, or duplicated, now existing on both filegroups?
I have googled for an article and checked BOL but couldnt find anything.
July 1, 2009 at 5:50 am
Hi,
Moving/Rebuilding the clustered index to an alternative filegroup will move the table data. This is because the clustered index IS the physical table data, so to speak.
To confirm, it will not create an additional copy of your data.
Here is an excellent walkthrough that will assist you in your efforts.
http://www.mssqltips.com/tip.asp?tip=1112
The only thing to watch out for is the LOB data will not be rellocated from one FILEGROUP to another when using the Clustered Index rellocation method. This is becuase LOB data is stored externally to the table data strucutre. In order to move these types of tables you will need to create a new table in the target filegroup and populate it manually.
July 1, 2009 at 7:07 am
You can also check with sp_help 'tablename' .You can see the new filegroup where the data is stored.
July 1, 2009 at 7:17 am
John is correct, the clustered index is the data (non-LOB, image, text, etc) and it moves when you rebuild the clustered index. There should be a better tool, but there isn't.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply