How to move a table from one file group to another file group in SQL server?

This is one of the questions haunting me from long where-in I was not aware of how to do that and I haven’t spent time as well to find the solution, luckily i tried this time. First thing is(as far as I tried) we cannot move a table from one file to another file in a database but what we can achieve is we can move a table between file groups. Something like from PRIMARY file group to SECONDARY and so on.

How?

To move a table from Filegroup1 to Filegroup2 we have to move the CLUSTERED INDEX of the table. After that we need to move the other NON-CLUSTERED indexes as well to the new file group. This will move the entire table from one FG to another FG.

Here I will move a table named ‘SalesOrderDetail’ of ‘AdventureWorks2012’ sample database from PRIMARY file group to SECONDARY file group. The below commands I have tried on both 2008 and 2012, they are valid for both versions.

Step: 1

To get Index Details:

To get the details of indexes along with file group information I am using below query. It will give table name, type of index, index name and in which file group it is currently in.

SELECT      'Table Name' = OBJECT_NAME(INX.object_id),

            'Index Name' = INX.name,

            'Type Of Index' = INX.type_desc,

            'FileGroup Name' = FGS.name

FROM        sys.indexes INX

INNER JOIN  sys.filegroups FGS ON INX.data_space_id = FGS.data_space_id

WHERE       INX.object_id = OBJECT_ID('[Sales].[SalesOrderDetail]')

 

 

As we can see in above capture the table has 3 indexes and all are in PRIMARY FG.

Creation of secondary file group and secondary file:

To create SECONDARY file group we can right click on the database and select ‘Properties’, then click on ‘Filegroups’, then click on ‘Add’ button and under the ‘Rows’ enter the name of the new file group, here I am giving the name as ‘SECONDARY’ itself and click ‘OK’. This will create new file group.

 

Adding file to new FG:

To create secondary data file and add it to new FG we can use below command:

Use AdventureWorks2012

ALTER DATABASE AdventureWorks2012

ADD FILE

( NAME = [AdventureWorksDW2008R2_Data2],

FILENAME = 'C:\SQLServer2012\AdventureWorks2012_Data2.mdf')

TO FILEGROUP [SECONDARY]

While creation itself we can specify the settings of the new data file like how it should grow, what should be its initial size and all. If we don’t specify those values it will take all default values. Through the above command a new file will be created with the name ‘AdventureWorks2012_Data2’ and it will reside in the location specified.

Moving of CLUSTERED INDEX:

After creating secondary FG and adding file to it , to move table ‘SalesOrderDetail’ to new FG we first need to move CLUSTERED INDEX of that table with below command:

CREATE UNIQUE CLUSTERED INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]

 ON [Sales].[SalesOrderDetail]

(

   [SalesOrderID] ASC,

      [SalesOrderDetailID] ASC

) WITH (DROP_EXISTING = ON,PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

 ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

ON [SECONDARY]

In the above command the 2 things important are DROP_EXISTING = ON this will create new one by dropping the existing one and ON [SECONDARY] this will create it in SECONDARY file group. With above command we are trying to move the ‘[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]’ to new FG. The index names can be obtained from same query of Step:1.

Note: If the table is very huge it will be faster if we truncate (Depends on wether you are allowed or not to TRUNCATE) the table before moving.

Once the command completes successfully if we run the query of step 1 it will give below results:

As we can see the CLUSTERED INDEX  has been moved to new FG. Now in the same way we need to move the other NON-CLUSTERED indexes as well to new FG.

CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail]

(

      [rowguid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,

 IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

 ALLOW_PAGE_LOCKS  = ON) ON [SECONDARY]

GO

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]

(

      [ProductID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,

IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [SECONDARY]

GO

After moving NON-CLUSTERED indexes as well we can verify with same query:

 

In capture we can see now all the indexes have been moved to new FG.

The same has to be done for all the indexes of the table. For moving of NON-CLUSTERED indexes just script out the existing indexes and set the 2 options as DROP_EXISTING = ON and ‘ON [SECONDARY]’.

 

Thanks!!