locked
Primary file group tables move userdefined file group RRS feed

  • Question

  • Hi Experts,

    I have to move Primary file group table to another(user defined ) file group could you any body help me

    Really appriciated. Those guys.

    Am using alter database move & modify it will not working

    Thanks & Regards
    Raj
    Wednesday, September 16, 2009 2:02 PM

Answers

  • Hi,

    you must use ALTER TABLE - http://msdn.microsoft.com/en-us/library/ms190273.aspx
    MOVE TO { partition_scheme_name ( column_name [ 1, ... n] ) | filegroup | "default" }
    Specifies a location to move the data rows currently in the leaf level of the clustered index. The table is moved to the new location.

    ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO FG_NEW)
    GO
    ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
    GO

    Or ....

    To move an existing table with a clustered index:
    CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
    ON FG_NEW
    GO 

    To move a non-clustered index:
    CREATE INDEX IDX_OrderID ON dbo.OrdersDetail(OrderID)
    WITH (DROP_EXISTING = ON)
    ON FG_NEW
    GO

    If the table does not have a clustered index and needs to be moved, you have to create  the clustered index on the table specifying the new filegroup:
    CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
    ON FG_NEW
    GO
    DROP INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
    GO

    I wish you a nice day,



    Torsten Schuessler
    Wednesday, September 16, 2009 2:57 PM
  • Raj

    The Clustered Index *IS* the table. When you move that, you have moved the table.

    However, the non-clustered indexes will not move simultaneously. You will have to move those separately, if required.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, September 16, 2009 3:07 PM

All replies

  • Hi Raj

    You need to rebuild the clustered index on the table (followed by the other indexes if required): CREATE INDEX with the DROP_EXISTING clause

    See the following:
    http://technet.microsoft.com/en-us/library/ms188783.aspx

    http://technet.microsoft.com/en-us/library/ms175905.aspx

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, September 16, 2009 2:34 PM
  • Hi Ewan,

    Thank you for your reply

    In a primary file group N no of table is there i have to move only 10 table to another file group can you please suggest me

    Thanks
    Wednesday, September 16, 2009 2:48 PM
  • Hi Raj

    For each of the 10 tables, you need to do the CREATE INDEX with DROP_EXISTING for the clustered index.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, September 16, 2009 2:50 PM
  • Hi,

    you must use ALTER TABLE - http://msdn.microsoft.com/en-us/library/ms190273.aspx
    MOVE TO { partition_scheme_name ( column_name [ 1, ... n] ) | filegroup | "default" }
    Specifies a location to move the data rows currently in the leaf level of the clustered index. The table is moved to the new location.

    ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO FG_NEW)
    GO
    ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
    GO

    Or ....

    To move an existing table with a clustered index:
    CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
    ON FG_NEW
    GO 

    To move a non-clustered index:
    CREATE INDEX IDX_OrderID ON dbo.OrdersDetail(OrderID)
    WITH (DROP_EXISTING = ON)
    ON FG_NEW
    GO

    If the table does not have a clustered index and needs to be moved, you have to create  the clustered index on the table specifying the new filegroup:
    CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
    ON FG_NEW
    GO
    DROP INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
    GO

    I wish you a nice day,



    Torsten Schuessler
    Wednesday, September 16, 2009 2:57 PM
  • Ewan

    How can move table  to create indexes of the tables

    as per your reply we create index with drop_existing  is  on  means

    (The existing index is dropped and rebuilt  ) for that purpose only we have to use how can we move primary file group tables (10) another files group

    Can you please suggest me......I think am not able to follow you could you please expline me with step by step






    Wednesday, September 16, 2009 3:05 PM
  • Raj

    The Clustered Index *IS* the table. When you move that, you have moved the table.

    However, the non-clustered indexes will not move simultaneously. You will have to move those separately, if required.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, September 16, 2009 3:07 PM
  • Hi Raj

    If your question has been answered can you mark any helpful posts, and any answers, to close the thread. This keeps the forums tidy and helps those who experience the issue in future to find relevant answers.

    Regards

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Tuesday, September 29, 2009 3:01 PM