How can I move a table to another filegroup in MS SQL Server?

Question

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

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?

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] 

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.

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

To answer this question, first we must understand

• If a table does not have an index, its data is called a heap
• If a table has a clustered index, that index is effectively your table data. Therefore, if you move the clustered index, you will also move your data.

The first step is to find out more information about the table we want to move. We do this by executing this T-SQL:

sp_help N'<<your table name>>' 

The output will show you a column titled 'Data_located_on_filegroup.' This is a handy way to know which filegroup your table data is on. But more important is the output that shows you information about the table's indexes. (If you only want to see information about the table indexes, just run sp_helpindex N'<<your table name>>') Your table may have 1) no indexes (so it's a heap), 2) a single index, or 3) multiple indexes. If the index_description starts with 'clustered, unique, ...', that is the index you want to move. If the index is also a primary key, that is OK, you can still move it.

To move the index, make a note of the index_name and index_keys shown in the results of the above help query, then use them to fill in the <<blanks>> in the following query:

CREATE UNIQUE CLUSTERED INDEX [<<name of clustered index>>] ON [<<table name>>]([<<column name the index is on - from index_keys above>>]) WITH (DROP_EXISTING = ON, ONLINE = ON) ON <<name of file group you want to move the index to>> 

The DROP EXISTING, ONLINE options above are important. DROP EXISTING makes sure the index is not duplicated, and ONLINE keeps the table online while you're moving it (now only available in Enterprise versions).

If the index you're moving is not a clustered index, then replace UNIQUE CLUSTERED above with NONCLUSTERED

To move a heap table, add a clustered index to it, then run the above statement to move it to a different filegroup, then drop the index.

Now, go back and run sp_help on your table, and check the results to see where your table and index data is now located.

If your table has more than one index, then after you run the above statement to move the clustered index, sp_helpindex will show that your clustered index is on the new filegroup, but any remaining indexes will still be on the original filegroup. The table will continue to function normally, but you should have a good reason why you want the indexes located in different filegroups. If you want the table and all its indexes to be in the same filegroup, repeat the above instructions for each index, substituting CREATE [NONCLUSTERED, or other] ... DROP EXISTING... as necessary, depending on the type of index you are moving.