Nisarg Upadhyay
View filegroup of SQL Database

How to move tables to another filegroup of a SQL database

December 14, 2021 by

This article explains the different methods to move tables between different filegroup of SQL databases.

We are going to learn the following scenarios:

  1. Moving tables with the clustered index
  2. Moving tables without a clustered index

We are going to move the tables by using the following methods:

  1. Create an index using the DROP EXISTING option as ON. This option is used when a clustered index (Primary key OR Unique Key) is defined on the table. This method is used when the table has a clustered index
  2. Create a new table and index on a different filegroup, copy data from the existing table and drop the old tables. This method is used when your table does not have any clustered index. This method is used when the table does not have clustered index

Environment Setup

For demonstration, I am using the Stackoverflow demo database. You can download it from this location. We want to move the largest table of the database to a different file group. To identify the top ten large tables of any database, you can run the following query.

View top 10 tables in SQL Database

The largest table is dbo.Posts so, we will move it to the secondary filegroup.

I have created a secondary filegroup named FG_StackOverflow2010_ Posts. The data file associated with the FG_StackOverflow2010_Posts will be created in D:\StackOverflow2010_ Posts location. The file name is StackOverflow2010_Posts.ndf. The initial size will be 9032704 KB, and the Filegrowth will be 524288 KB. Run the following query to create a filegroup:

Query to create a secondary data file:

You can view the list of file groups and their files by running the below query:

View filegroups

We are transferring the table named dbo.Posts to filegroup named FG_StackOverflow2010_Posts. First, let us understand how to move the table to the secondary filegroup named by re-creating the clustered index.

Move table that has clustered index

The leaf nodes of the clustered index contain the actual data; therefore, moving the clustered index will copy the data to another filegroup. The table dbo.Posts have a clustered index named IDX_Posts_ID. This index is in the Primary filegroup. Now, we will run the create index statement with the option. The following query creates the index in FG_StackOverflow2010_Posts.

Now, verify the table has been moved or not. The following query provides the list of tables with their filegroup.

Filegroup has been moved

Alternatively, you can view the filegroup of the table in table properties of the SQL database. Right-click on dbo.Posts 🡪 Select Properties 🡪 In the properties dialog box, click on Storage.

Table property of SQL database

As you can see, the table has been moved to FG_StackOverflow2010_ Posts.

Move the table that has a non-clustered index

To move the table without a clustered index, we can use the following steps:

  1. Create a new destination table and index in the secondary filegroup using the definition of the source table. The index and table names must be different
  2. Use SQL Server import-export wizard to copy the data between the tables

First, Extract the definition of the table. To do that, Right-click on dbo.Posts table🡪 Select Script Table as option 🡪 Select New Query editor window.

Now, create a new table named dbo.Posts_New in FG_StackOverflow2010_ Posts filegroup using an extracted definition.

Once a table is created, launch SQL Server import export wizard. The first screen provides information about the wizard, so you can skip it.

Import export wizard

On Choose Data Source screen, specify the following options:

  1. Data Source: Specify the appropriate data source; I have selected SQL Server Native Client 11.0
  2. Server Name: Either specify (Local), or you can specify Server Name\Instance Name. I have selected Nisarg-PC\SQL01
  3. Authentication: Specify the authentication method. I have selected Windows Authentication
  4. Database: Specify the source database name. I have selected StackOverFlow2010

The selected options look like the following screenshot. Click Next.

Import export wizard: Choose Data source

We are copying data on different tables of the same SQL Database, so on the Choose a Destination screen, I have selected the same options that I have selected in Choose the Source screen. Click Next.

Import export wizard: Choose destination Data source

On the specified table Copy or Query screen, we can choose the following option:

  1. Copy data from one or more tables or views: This option is used to copy the entire data of the source table to a destination table
  2. Write a query to specify data to transfer: This option is used to copy the specific data from one or multiple tables

In our demo, I have selected Copy data from one or more tables or view options.

Import export wizard: Choose copy method

On Select Source Tables and Views screen, you can select the source and destination tables and views. We are moving dbo.Posts table; therefore, select checkbox on dbo.Posts table in Source column and select dbo.Posts_New in the destination column. Click Finish.

On the Complete the Wizard screen, you can see the tasks that the wizard will perform. Click on Finish.

select Source and destination

Complete the wizard

The import-export process begins.

Data is copying

The process has been completed successfully.

Data has been copied

Once it is completed, let us verify that the table has been moved or not.

View filegroup of SQL Database

Now, rename the source and destination tables and drop the source tables from the SQL Database by running the below query.

Summary

In this article, we learned how to move the table between the filegroup of the SQL database. We have learned the following methods:

  1. Moving tables with the clustered index. In this method, we learned how we could re-create the index with the DROP EXISTING option
  2. Moving tables with the non-clustered index. In this method, we learned SQL Server import-export method to copy tables
Nisarg Upadhyay
6,416 Views