如何将表移动到MS SQL Server中的另一个文件组? [英] How can I move a table to another filegroup in MS SQL Server?

问题描述

我有两个大表的SQL Server 2008 Ent和OLTP database.如何在不中断服务的情况下将这些表移动到另一个filegroup?现在，这些表中每秒插入大约100-130条记录，并且每秒更新30-50条记录.每个表有大约1亿条记录和6个field(包括一个字段geography).

I have SQL Server 2008 Ent and OLTP database with two big tables. How I can move these tables to another filegroup without service interrupting? Now, about 100-130 records inserted and 30-50 records updated each second in these tables. Each table have about 100M records and six fields (including one field geography).

我正在Google中寻找解决方案，但是所有解决方案都包含

I am looking for a solution in Google, but all solutions contain

创建第二个表，从第一个表中插入行，删除第一个表， 等等

create second table, insert rows from first table, drop first table, etc

我可以使用分区功能来解决此问题吗?

Can I use partitioning functions for solving this problem?

推荐答案

如果只想将表移动到新文件组，则需要在表上重新创建聚簇索引(毕竟:聚簇索引是表数据)在您想要的新文件组上.

If you want to just move the table to a new filegroup, you need to recreate the clustered index on the table (after all: the clustered index is the table data) on the new filegroup you want.

您可以通过以下方式进行此操作:

You can do this with e.g.:

CREATE CLUSTERED INDEX CIX_YourTable
   ON dbo.YourTable(YourClusteringKeyFields)
   WITH DROP_EXISTING
   ON [filegroup_name]

或者如果您的聚集索引是唯一:

or if your clustered index is unique:

CREATE UNIQUE CLUSTERED INDEX CIX_YourTable
   ON dbo.YourTable(YourClusteringKeyFields)
   WITH DROP_EXISTING
   ON [filegroup_name]

这将创建一个新的聚簇索引并删除现有的聚簇索引，并在您指定的文件组中创建新的聚簇索引-瞧，您的表数据已移至新的文件组.

This creates a new clustered index and drop the existing one, and it creates the new clustered index in the file group you specified - et voila, your table data has been moved to the new filegroup.

有关所有可用内容的详细信息，请参见有关CREATE INDEX的MSDN文档您可能要指定的选项.

See the MSDN docs on CREATE INDEX for details on all available options you might want to specify.

这当然还没有涉及分手，但这完全是另外一回事了...

This of course doesn't yet deal with partioning, but that's a whole other story all to itself...

这篇关于如何将表移动到MS SQL Server中的另一个文件组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！