Moving Index in Separate Filegroup

By default the index are stored in a same file group as the base table on which the index is created. It is important that if we have a very large table with index. To improve the performance of the query we have to move the index in a separate file group (Better in different Drive/Hard disk) to improve I/O performance.

But we can do the following:

1.    Create nonclustered indexes on a filegroup other than the filegroup of the base table.

2.   Partition clustered and nonclustered indexes to span multiple filegroups.

3     Move a table from one filegroup to another by dropping the clustered index and specifying a  

     new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX      statement 

      or by using the CREATE INDEX statement with the DROP_EXISTING clause.

      

Some points should be taken care of:

1.     If a table has a clustered index, moving the clustered index to a new filegroup moves the table to 

     that filegroup.

2.     You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using   

     Management Studio. To move these indexes use the CREATE INDEX statement with the 

    (DROP_EXISTING=ON) option in Transact-SQL.

The scripts gives us an idea, how to move the index in different file group.

USE my_db;

GO

/*

    Creates the TranFG filegroup on the my_db database

*/

ALTER DATABASE my_db

ADD FILEGROUP TranFG;

GO

/*

  Adds the TranFGData file to the TranFG filegroup. Please note that you

  will have to change the

  filename parameter in this statement to execute it without errors.

*/

ALTER DATABASE my_db

ADD FILE

(

    NAME = TranFGData,

    FILENAME = 'C:\Program Files\Microsoft SQL

                Server\MSSQL11\MSSQL\DATA\TranFGData.ndf',

    SIZE = 5MB,

    MAXSIZE = 100MB,

    FILEGROWTH = 5MB

)

TO FILEGROUP TranFG;

GO

/*

  Creates the IX_Employee_OrganizationLevel_OrganizationNode index

  on the TransactionsPS1 filegroup and drops the original

  IX_Employee_OrganizationLevel_OrganizationNode index.

*/

CREATE NONCLUSTERED INDEX IX_Cust

    ON My_Cust (CustId)

    WITH (DROP_EXISTING = ON)

    ON TranFG;

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS