question

Moving table between FileGroups depends on whether the table is Clustered or it is Heap. 1. For Clustered table DROP the Clustered index and create it on the other filegroup using then ON keyword with Target FileGroup. You have also an option to use the MOVE TO keyword as part of the DROP INDEX statemetn to move the heap to particular File Group. Then you do not need to specify the ON clause in the CREATE INDEX as when not specified, then the index is created on the same file group as the original table. You can use also ALTER TABLE to DROP CONSTRAINT (if the Clustered index is PRIMARY KEY). 2. For Heap, you can again create a clustered index and then DROP it to move the table. Remember that if there are some additional non clustered indexes those will not be moved automatically an dwill have to be re-created on the new file groups if those should be moved too. Anyway it is better to drop all the non clustered indexes prior moving and then re-create them (as in case creating or dropping clustered index all non clustered indexes are rebuilt too - so in this case they are not dropped prior moving, they will be rebuilt 2 times). Also if possible and you have a space, you can create an additional file group to hold intermediate result (HEAP after dropping the clustered index). Use the DROP INDEX with the MOVE TO keyword to move the heap to temp file group and then create a new clustered index on the target file group. This will allow you to keep the file groups not fragmened and the final DB size will not increase much if those tables are big. As yo put the intermediate result on temp file group which you can delete after the movement is finished (addtitional space will be allocated in the temporary filegroup and not in the source or target file group). You will also keep the logical fragmentation low. The mentioned commads also allows you to change the partition scheme for the table if the table is partitioned. See: [DROP INDEX (Transact-SQL)][1] [CREATE INDEX (Transact-SQL)][2] [ALTER TABLE (Transact-SQL)][3] [1]: http://msdn.microsoft.com/en-us/library/ms176118.aspx [2]: http://msdn.microsoft.com/en-us/library/ms188783.aspx [3]: http://msdn.microsoft.com/en-us/library/ms190273.aspx

First of all, you should have a solid reason for moving the table to new filegroup most preferably on a different drive. Secondly, you should keep in mind that there could be downtime required. So you have to plan accordingly. There are some solutions (already told by Mr. Pavel) , but the best solution would depend upon your environment and data structures. If you have that table without any dependencies, without B/LOB data type etc. data, and you do not want to move such data, it will work great. Moreover, In addition to Mr. Pavel's answer, if you have ENTERPRISE edition, you could use index build ONLINE option to move the data without a downtime (some conditions do apply) Keeping in mind the limitation of moving B/LOB columns etc., non-flexibility of the CREATE WITH DROP_EXISTING clustered index and table data could be huge, my preference is something like (Hope not missing something) - TAKE BACKUP OF THE DATABASE - MAKE SURE THE NEW FILE HAS SIZE AT LEAST EQUAL TO THE TABLE - GET ALL DEFINITIONS RELATED TO THE TABLE (WOULD BE USED FOR NEW TABLE) - CHANGE THE RECOVERY MODEL TO SIMPLE OR BULK_LOGGED - CREATE COPY (JUST MAIN STRUCTURE)OF THE TABLE ON THE NEW FILE GROUP - DROP FOREIGN KEYS IF ANY - DROP NON-CLUSTERED INDEXES / MANUAL STATISTICS (OFTEN OVERLOOKED) - CREATE CLUSTERED INDEXANF NOT ANY ON EXISTING TABLE (PREFERABLY IDENTITY COLUMN) - MOVE THE DATA IN BATCHES (SORTED IF POSSIBLE) TO KEEP THE LOG FILE IN CHECK IN CASE OF SPACE CONSTRAINTS - BACKUP LOG IF POSSIBLE - VALIDATE THE DATA IF POSSIBLE - CREATE CLUSTERED INDEX ON NEW TABLE (IF NOT POSSIBLE BEFORE) - CREATE NON-CLUSTERED INDEXES / MANUAL STATISTICS - VALIDATE REFERENTIAL INTEGRITY BY CREATING FOREIGN KEYS IF ANY At the moment I can only remember these. The sequence and the options could change according to the environment.

Omri Bahat wrote a script which did the movements, but that's now owned by RedGate. Maybe you can found it on the internet, otherwise I'll have the code for you.