CODEWITHSUNDEEP
sqlsql-serverpartitioningfilegroup
Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 1

Views: 3014

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

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]

Upvotes: 0

under
under

Reputation: 3067

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

Upvotes: 2

Related Questions