Change table file group filegroup

Hi There

I am running SQL Server 2005 Enterprise Edition, i want to split my data and indexes on different drives.

In 2000 i had to recreate clustered indexes and non clustered indexes on the correct filegroups to accomplish this.

In 2005 i see there is a ALTER TABLE MOVE TO Filegroup option, thats cool.

Does this effectively do the same as rebuilding the clustered index on the new filegroup? Will this leave the other indexes of the table on the primay filegroup or move them as well ?

If i wanted to also move the non clustered indexes is there a better way to move them that drop and re-create on the new filegroup in 2005, i see the ALTER INDEX statement does not support a move to filegroup option.

In a nutshell what is the best/easiest way to move exisitng table data and indexes to new file groups in Sql Server 2005 Enterprise Edition?

Thanx

Hi

ALTER TABLE MOVE TO should only be used when droppin gthe old clustered index.

You cannot move the table and retain the index with above.

I guess the best thing to use is drop and create index. You can use ONLINE option (Enterprise Edition Only) so that there is no downtime.

Jag

|||

Hi Jag

I am not following you, i am using the ALTER table command i am not dropping any clustered index?

I was asking what is happening in the background.

Also you say "You cannot move the table and retain the index with above.", a re you saying you loose your indexes with this command, i find that hard to believe?

Please clarify?

|||

Ok

Sorry about the confusion.

What I meant was that, "MOVE TO clause is only available with ALTER TABLE when you do a DROP CONSTRAINT"

It is not available with ALTER TABLE on its own.

The command will look like this:

Code Snippet

alter table t1 drop constraint PK_t1 with (move to [second]);

You cannot have the following:

Code Snippet

alter table t1 move to [second]);

Jag

|||

Hi Jag

Ok cool i get it now.

That is really strange that you have to drop a constraint, and i would have thought 2005 would have provided an easier way to move filegroups for tables data and indexes?

So you reckon the drop an re-create clustered index is till the way to go ?

|||

Yes I think so.

Drop and create the index to move filegroups.

good luck and let us know how you get on.

Jag

|||

Hi Jag

Ok cool, i just find that weird that there is no better way in 2005, so basically i will do it exactly as i did in 2000.

ALso not so easy to do when you have to move hundreds or thousands of tables and their indexes.

Basically i have to cursor through all the tables drop indexes, dynmically re-create the index defintion from sysindexes and re-create the index, not very clean, please let me know if you can think of a better way.

Thanx

|||Hello,

So isn't there any way to move a table to another filegroup without any lost (PK;FK,RelationShip).

I have more than 100 tables in my database and i created 5 filegroups. I have move them in their filegruop. Is there any shortest way to move?

Thank you very much...|||

Hi

Exactly what i was wondering the whole time, but it seems there still is no better way to move filegroups than to have to drop and rebuild the PK clustered indexes etc, i am in the same situation, database with hundreds of tables and thousands of PK - FK relationships and no easy way to move them to new filegroups.

Thanx

|||

really big problem and you can't create a table on a filegroup (without code) when you using design tools

please solve this problem !!!