Efficient way to change the table's filegroup

Question

I have around 300 tables which are located in different partition and now these tables are not in use for such huge data as it was. Now, I am getting space issue time to time and some of but valuable space is occupied by the 150 filegroups that was created for these tables so I want to change table's filegroup to any one instead of 150 FG and release the space by deleting these filegroups.

FYI: These tables are not holding any data now but defined many constraints and indices.

Can you please suggest me, how it can be done efficiently ?

Possible duplicate of [How I can move table to another filegroup?](http://stackoverflow.com/questions/2438019/how-i-can-move-table-to-another-filegroup) — under, Dec 02 '16 at 09:17

score 2 · Answer 1 · answered Dec 02 '16 at 09:14

2

To move the table, drop and then re-create its clustered index specifying the new FG. If it does not have a clustered index, create one then drop it.

It is best practice not to keep user data on primary FG. Leave that for system objects, and put your data on other file groups. But a lot of people ignore this...

answered Dec 02 '16 at 09:14

under

2,519
1
21
40

right I actually don't want to use primary FG but want to use one FG instead of 150 FG. Sorry for my mistake let me add this in the question. So we don't have any other way to address this situation ? – Shushil Bohara Dec 02 '16 at 09:18
To move a table you just drop and re-create its clustered index. Not a problem. The comment about PRIMARY filegroup was just FYI... – under Dec 02 '16 at 09:19

score 0 · Accepted Answer · answered Dec 13 '16 at 13:09

I found few more information on the ways of changing the FG group of existing table:

1- Define clustered index in every object using NEW_FG (Mentioned in @under answer)

CREATE UNIQUE CLUSTERED INDEX <INDEX_NAME> ON dbo.<TABLE_NAME>(<COLUMN_NAME>) ON [FG_NAME]

2- If we can't define clustered index then copy table and data structure to new table, drop old and rename new to old as below

Changes Database's default FG to NEW_FG so that every table can be created using INTO, under that new FG by default

ALTER DATABASE <DATABASE> MODIFY FILEGROUP [FG_NAME] DEFAULT

IF OBJECT_ID('table1') IS NOT NULL 
BEGIN
    SELECT * INTO table1_bkp FROM table1
    DROP TABLE table1
    EXEC sp_rename table1_bkp, table1
END

After all the operation Database's default FG as before

ALTER DATABASE <DATABASE> MODIFY FILEGROUP [PRIMARY] DEFAULT

3- Drop table if feasible then create it again using NEW_FG

DROP TABLE table1 
CREATE TABLE [table1] ( 
    id int,
    name nvarchar(50),
    --------
) ON [NEW_FG]

Efficient way to change the table's filegroup

2 Answers2

Linked