Post reply

Move Index from Primary File Group to Another File Group

  • January 29, 2007 at 1:57 pm

    #117458

    Hi Everyone,

    I need to move indexes from Primary File Group to another new file group. Would anyone please help me if there is a T-SQL statement to do that? I know one method that can be accomplish this task through GUI (Index Properties window in Management studio and click on storage and change the file group to the new file group you wanted).

    Additional Infomration:

    My database has only primary and log file groups and all data and indexes are sitting on same file group and file name. Now the disk is full and all also performance is very poor since data and indexes are on the same drive. Now I would like to move the indexes to different file that sits on different drive.  I couldn't find the right option in Alter Table, Alter Database etc...to move indexes from Primary to new file group. Any help is greatly appreciated.

    Thanks,

    BK

     

  • January 29, 2007 at 2:18 pm

    #686277

    If you use Enterprise manager to make the changes, you have the option to click the script button to see the script that it would use to make the change, and you can save that script if you want to.  Then just cancel making the change in Enterprise manager.

     

     

     

     

  • January 29, 2007 at 4:08 pm

    #686314

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

    CREATE INDEX Employee_Ind ON Employee(LName,FName,Minit) WITH DROP_EXISTING ON SECONDARY

     

    MohammedU
    Microsoft SQL Server MVP

  • January 30, 2007 at 10:26 am

    #686524

    Thank you folks. It really helped me.

    BK

     

Viewing 4 posts - 1 through 3 (of 3 total)

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