Efficient Way To Change The Table's Filegroup

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 s

Solution 1:

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...

Solution 2:

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') ISNOTNULLBEGINSELECT*INTO table1_bkp FROM table1
    DROPTABLE 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

DROPTABLE table1 
CREATETABLE [table1] ( 
    id int,
    name nvarchar(50),
    --------
) ON [NEW_FG]