问题描述
我大约有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]
这篇关于更改表文件组的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！