Moving a Table to a Different Filegroup

Category : Tips Published : August 1, 2015 User Rating : 4 Stars      Views : 4 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




Introduction

By default a database has a single filegroup called PRIMARY and all tables are stored in this filegroup. However it’s possible to create other filegroups and store tables in these other filegroups. One of the most common reasons for doing this is if you have some high performance disks and want to store certain tables on those faster disks. This is especially useful for data warehouses and other large databases where you might have some data that is accessed more frequently and where response times are particularly important.

This article shows how to create another filegroup and move existing tables into the new filegroup (if you are creating new tables then you can just create them on the new filegroup). I have used the AdventureWorks2008R2 demo database which can be downloaded from the CodePlex website.

Identifying which Filegroup a Table is currently on

To check which filegroup a table is currently on, just run the following query :
SELECT    tbl.name AS [Table Name],
          CASE WHEN dsidx.type='FG' THEN dsidx.name ELSE '(Partitioned)' END AS [File Group]
FROM      sys.tables AS tbl
JOIN      sys.indexes AS idx
ON        idx.object_id = tbl.object_id
AND       idx.index_id <= 1
LEFT JOIN sys.data_spaces AS dsidx
ON        dsidx.data_space_id = idx.data_space_id
ORDER BY  [File Group], [Table Name]
This query lists all tables and their associated filegroup, which will be ‘PRIMARY’ if you’ve just used defaults. If the table is partitioned, and therefore potentially stored on multiple filegroups, it will be listed as ‘(partitioned)’.

This is the result I get for the first few tables in my database :
Table filegroup listing

You can also get this information for a specific table by looking at the Storage page of the Properties window of the table in SQL Server Management Studio (to see the Properties window right click on the table in Object Explorer and select ‘Properties’) : Table properties filegroup

Creating a New Filegroup

If the filegroup you want to move the table to doesn’t already exist then it will need creating. To do this right click on the database within Object Explorer and select Properties. In the properties window select the ‘Filegroups’ item and then click the ‘Add’ button. The name of the new filegroup can then be typed into the text box as shown below (I’ve chosen to call it SECONDARY) :  Add new filegroup

The next stage is to go to the ‘Files’ page in the same Properties window and add a file to the filegroup (a filegroup always contains one or more files) using the ‘Add’ button. A logical name and sizing information can also be set up as shown below (make sure you select the new filegroup) :  Add new file to filegroup

Click OK to create the new filegroup and file.

Moving an Existing Table to the new Filegroup

To move a table to a different filegroup involves moving the table’s clustered index to the new filegroup. While this may seem strange at first this is not that surprising when you remember that the leaf level of the clustered index actually contains the table data. Moving the clustered index can be done in a single statement using the DROP_EXISTING clause as follows (using one of the AdventureWorks2008R2 tables as an example) :
CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID
ON HumanResources.Department(DepartmentID)
WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY
This recreates the same index but on the SECONDARY filegroup, this can be checked by looking at the table’s properties again (compare this to the first screenshot above): Table properties secondary filegroup

You may be wondering how to move a table the filegroup of a table without a clustered index (i.e. a heap). As far as I know the only way is to temporarily add a clustered index on the new filegroup and then drop it (if necessary).

Finally if you are creating new tables then there is no need to create them on the PRIMARY filegroup and move them, you can just create them straight onto the new filegroup.

Summary

This article has shown how to create a new filegroup and move an existing table into this filegroup (or any other filegroup).


Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Moving a Table to a Different Filegroup.aspx

Keywords

SQL 2012, 2008, 2008R2, filegroup, table


Comments
Post by TechnoCaveman on Sun 31 Jul 2016 18:44. Report Inappropriate Post

Step by step. Thank you. Very surprised others have not commented with same.
Post by Bill on Fri 19 Aug 2016 03:53. Report Inappropriate Post

this only works in Enterprise edition
Post by Javad on Thu 26 Jan 2017 14:17. Report Inappropriate Post

Good
Post by jab on Wed 12 Apr 2017 14:38. Report Inappropriate Post

awesome ...nice step by step instructions for a newbie DBA
Post by Aamer on Thu 10 May 2018 10:51. Report Inappropriate Post

Can we partition tables on to a separate server or if I put my question in another way than can we create filegroups or ndf file on to a separate server disk. I do know that we can do this on same server but different drive if change the ndf file path but I want to know is it possible to give it a separate server path.
Post by Hamid on Sun 12 May 2019 09:33. Report Inappropriate Post

Very good . thank you.
Post by Ahmed Saeed on Tue 14 May 2019 13:58. Report Inappropriate Post

I successfully created a new Filegroup and moved a large table to it from PRIMARY. but when checking the .mdf files, there is no major changes in the .mdf sizes. also, tried to shrink the database, but still no change.

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered