How can I move a table to another filegroup?

Question

I have a SQL Server 2008 Ent and OLTP database with two large tables. How can I move these tables to another filegroup without interruption of service? Now added about 100-130 records and 30-50 records updated every second in these tables. Each table has about 100 M records and six fields (including one field geography).

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

Can I use partition functions to solve this problem? Thank.

Answer 1

If you just want to move the table to a new file group, you need to recreate the clustered index in the table (after all: the clustered index is the table data) in the new file group that you want.

You can do this, for example:

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 deletes the existing one, and creates a new clustered index in the file group you specify - et voila, your table data has been moved to a new file group.

For more information on all the available options that you can specify, see the MSDN docs in CREATE INDEX .

This, of course, is not yet related to partioning, but that the whole other story is all for myself ...

Answer 2

To answer this question, we must first understand

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

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

 sp_help N'<<your table name>>' 

The output column will be "Data_located_on_filegroup". This is a convenient way to find out which filegroup your table data is included in. But more important is the output, which shows you information about table indexes. (If you want to see information about table indexes, just run sp_helpindex N'<<your table name>>' ). Your table may have 1) no indexes (so this is a bunch), 2) one index, or 3) several indexes. If index_description starts with "clustered, unique, ...", this is the index you want to move. If the index is also the primary key, that’s fine, you can still move it.

To move the index, look at the index_name and index_keys shown in the results of the above help query, then use them to populate <<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, ONLINE ON <<name of file group you want to move the index to>> 

DROP EXISTING, ONLINE values ​​above. DROP EXISTING ensures that the index is not duplicated, and ONLINE saves the table on the network while you move it.

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

To move the heap table, add a clustered index to it, then run the above statement to move it to another file group, and then release the index.

Now go back and run sp_help in your table and check the results to see where the table and index data are now.

If your table has several indexes, then after running the above statement to move the cluster index, sp_helpindex will show that your cluster index is in the new file group, but any remaining indexes will still be in the original file group. The table will continue to function normally, but you should have a good reason why you need 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, replacing CREATE [NONCLUSTERED, or other] ... DROP EXISTING... as needed, depending on the type of index you are moving.

Answer 3

Partitioning is one solution, but you can “move” the clustered index to a new filegroup without interrupting service (subject to certain conditions, see the link below) using

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

A clustered index is data, and it is the same as moving a filegroup.

See CREATE INDEX

It depends on whether your primary key is grouped or not, which changes the way we do it

Answer 4

This excerpt from SQL Server Books Online says everything: “Since the level of the clustered index sheet and data pages are by definition the same, creating a clustered index and efficiently using the on_scheme_name or ON element of filegroup_name moves the table from the filegroup on which the table was created, to a new partition scheme or filegroup . " (Source - http://msdn.microsoft.com/en-us/library/ms188783.aspx ) from ( http://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database -filegroups / )

as other friends said, such as the accepted marc_s answer, the following screenshot gives you another way to do this using the SSMS GUI.

note that you can easily switch to another filegroup from the index property on the storage tab

Answer 5

How to move a table to another filegroup?

NOTE. Moving a table to another filegroup only works with Enterprise Edition.

Step 1:

Check where the filegroup table is located:

 -- Query to check the tables and their current filegroup: SELECT tbl.name AS [Table Name], CASE WHEN dsidx.type='FG' THEN dsidx.name ELSE '(Partitioned)' END AS [File Group] FROM sys.tables AS tbl JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id AND idx.index_id <= 1 LEFT JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id ORDER BY [File Group], [Table Name] 

Step 2:

Move existing table / s to a new filegroup

If the file group to which you want to move the table no longer exists, create an additional file group, and then move the table.

To move a table to another filegroup, you need to move the clustered index of the tables to the new filegroup. The cluster index sheet level actually contains table data. Therefore, moving a clustered index can be performed in a single expression using the DROP_EXISTING clause as follows:

 CREATE UNIQUE CLUSTERED INDEX [Index_Name] ON [SchemaName].[TableName] ( [ClusteredIndexKeyFields] )WITH (DROP_EXISTING = ON, ONLINE = ON) ON [FilegroupName] GO 

Step 3:

Move the remaining nonclustered indexes to the secondary filegroup

You need to move indexes without clustering manually using the following syntax:

 --1st check the index information using the following sp sp_helpindex [YourTableName] --Now by using the following query you can move the remaining indexes to secondary filegroup CREATE NONCLUSTERED INDEX [Index_Name] ON [SchemaName].[TableName] ( [IndexKeyFields] )WITH (DROP_EXISTING = ON, ONLINE = ON) ON [FilegroupName] GO 

Moving a heap to another filegroup:

As I know, the only way to move the heap to another filegroup is to temporarily add the clustered index to the new filegroup and then delete it (if necessary).

Answer 6

I think these steps are very simple and straightforward to move any table to another group of files (via Management Studio):

• Move all non-clustered indexes to a new file group by simply changing the FileGroup property for each index

• Change the index of the cluster to a non-cluster and just change its group of files (as in the previous step)

• Add a new temporary cluster index using the “new file group” through this command (or via the IDE):

    CREATE CLUSTERED INDEX [PK_temp] ON YOURTABLE([Id]) ON NEWFILEGROUP 

  (the above command moves all data to a new group of files)

• Delete the previous temporary PK (when it does its work in the prefecture!)

• Change the main cluster index again to the cluster index (again through the IDE)

The advantage of the above steps is not to drop the existing FK relationship. Also, using the IDE prevents data loss in the face of errors.

NOTE. Make sure that the disk quota is not enabled for your filegroup or that it is not set correctly. Otherwise, you will get a "filegroup is full" exception!

Answer 7

 CREATE CLUSTERED INDEX IXC_Products_Product_id ON dbo.Products(Product_id) WITH (DROP_EXISTING = ON) ON MyNewFileGroup