Post reply

How to... Change a Filegroup for an Existing Table

  • February 19, 2004 at 9:54 am

    #163134

    Hi,

    What is the syntax to change the filegroup for an existing table object and index? I was also thinking of using "sp_msforeachtable" to do multiple objects.

    Thanks. Jeff

    Many thanks. Jeff

  • February 19, 2004 at 10:14 am

    #494744

    CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

        ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

    [ WITH < index_option > [ ,...n] ]

    [ ON filegroup ]

    For moving existing nonclustered index, You have to drop the index and recreate it in new filegroup.

    If you want to move the table into new filegroup, create the clustered index on the table in the new filegroup. You can drop the clustered index later if you don't need clustered for the table.

  • February 19, 2004 at 10:54 am

    #494752

    Hi, thanks. But I'm not creating new tables ... I want to ALTER the existing tables ... ???

    Many thanks. Jeff

  • February 19, 2004 at 11:01 am

    #494754

    ... and you are if you recreate the clustered index as Allen Cui mentioned above!


    * Noel

  • February 19, 2004 at 11:59 am

    #494766

    Is ther not a statement like:

    ALTER TABLE MyTable ON [NewFileGroup]

    ??

    Many thanks. Jeff

  • February 19, 2004 at 12:03 pm

    #494768

    No.

    Alter table modifies a table definition by altering, adding, or dropping columns and constraints, or by disabling or enabling constraints and triggers. See BOL for complete syntax.

  • February 20, 2004 at 3:10 am

    #494886

    Cant use alter table or anything simple. I assume you know you can do it via design table in Ent Mgr

  • February 20, 2004 at 8:34 am

    #494939

    Using EM? I was hoping for something similar to ORACLE, which allows you to move objects to new filegroups/tablespaces using the ALTER syntax for the given object type ie: table/index. Maybe Microsoft is changing this???

    Thanks everyone for your help ...

    Many thanks. Jeff

  • February 20, 2004 at 12:30 pm

    #494991

    If you simply recreate the clustered index of a table on a different filegroup it will move the whole set of data in that table to the new filegroup? That makes sense but it seems too easy...

  • February 20, 2004 at 2:33 pm

    #495006

    Would you like it better when it was way more complicated?

    The leaf level of a clustered index is the actual table data. So when you recreate it on another filegroup you actually move the data there.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

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