How I can move table to another filegroup ?

Question

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

I looking for solution via google, but all solutions contain "create second table, insert rows from first table, drop first table, bla bla bla".

Can I use partitioning functions for solving this problem? Thank you.

Answer 1

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]

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.

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

Answer 2

Partitioning is one solution, but you could "move" the clustered index to the new filegroup with no service interruption (subject to some conditions, see link below) using

CREATE CLUSTERED /*oops*/ INDEX ... WITH (DROP_EXISTING = ON, ONLINE = ON, ...) ON newfilegroup

The clustered index is the data and this is the same as moving filegroup.

Please see CREATE INDEX

This depends on if your primary key is clustered or not, which changes how we'd do it