更改表文件组的有效方法 [英] Efficient way to change the table's filegroup

问题描述

我大约有300个表位于不同的分区中，现在这些表不再用于处理如此庞大的数据.现在，我不时收到空间问题，为这些表创建的150个文件组占用了一些但宝贵的空间，因此我想将表的文件组更改为任意一个而不是150 FG，并通过删除这些文件组来释放空间

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 ?

推荐答案

我发现了有关更改现有表的FG组的方法的更多信息:

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

1-使用NEW_FG(在@under答案中提及)在每个对象中定义聚簇索引

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-如果无法定义聚簇索引，则将表和数据结构复制到新表中，删除旧文件，并将新文件重命名为旧文件，如下所示:

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

将数据库的默认FG更改为NEW_FG，以便可以使用INTO在默认情况下在该新FG下创建每个表

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

所有操作都像以前一样操作数据库的默认FG

After all the operation Database's default FG as before

ALTER DATABASE <DATABASE> MODIFY FILEGROUP [PRIMARY] DEFAULT

3-删除表(如果可行)，然后使用NEW_FG重新创建

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] 

这篇关于更改表文件组的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！