Post reply

move tables from one filegroup to another

  • November 15, 2002 at 4:31 pm

    #174735

    Hello everyone, what is the best way to move a table (with all data, permissions, constraints, etc.) from one filegroup to another? Thank you.

  • November 15, 2002 at 4:49 pm

    #441537

    Do it in Enterprise manager and generate the script.

    Basically you have to rename the table. recreate the table in the new filegroup and then copy the data.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 16, 2002 at 2:30 pm

    #441573

    Thank you. However, I have a dilema.

    I have some tables with clustered index and they are created on my index filegroup. I want to move them to my primary filegroup. I was thinking that I would go to EM and remove the clustered index, change the filegroup, save the changes and then re-create the clustered index.

    I tested this scenario on the development computer. Then I tested it on production and I got two different results.

    On the development box, when I check the "Create as CLUSTERED" option, the filegroup changes from index to primary, which is what I want. In other words, when you want to create a clustered index, you can only create it on the primary filegroup and EM won't let you create it on the index filegroup.

    On the production box, I removed the clustered index, changed the filegroup to primary, and saved the changes. I went back and checked the "Create as CLUSTERED" checkbox and the filegroup changes from primary to index. It won't let me create a clustered index table on the primary filegroup.

    These two boxes are acting opposite from each other. Any idea why this is happening?

  • November 18, 2002 at 2:37 am

    #441594

    Use your favorite SQL Query Analyzer and type:

    drop index TableName.IndexName

    Where IndexName is your clustered index

    And the type:

    create clustered index IndexName on TableName(column, column...) on NewFileGroup

    This one has effect to place your table on the new filegroup because the data will "follow" the clustered index



    Bye
    Gabor

  • November 18, 2002 at 6:06 am

    #441606

    The clustered index has to exist in the same filegroup as the data, because the base of the index is the data.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • June 20, 2005 at 7:45 am

    #566721

    You can find a script that would move the data, indexes, and more to a new filegroup on

    http://education.sqlfarms.com/ShowPost.aspx?PostID=59

    Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply