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.

Answer 3

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

Answer 4

Please note, that recreating the clustered index only moves the "primitive" columns, like int, bit, datetime etc.

To move varchar(max), varbinary and other "blob" columns you have to recreate the table. Thankfully, there's a way to do this semi-automatically in SSMS - by changing the "text filegroup" in the table "design" window, and then saving changes.

(Quick update from 2021): Alternatively you can create a temporary "partitioning" rule (a partitioning rule is a function that decides which filegroup the data goes to) that will point to the new filegroup for all values in the table. Applying this partitioining scheme will actually move the data

I blogged about this here: https://www.jitbit.com/alexblog/153-moving-sql-table-textimage-to-a-new-filegroup/ if you need more details.

Answer 5

This excerpt from SQL Server Books Online says it all: "Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the 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 already said by other friends like accepted answer by marc_s following is the screenshot gives you another way to do it using SSMS GUI.

please note that you can move to another filegroup easily of the index property in storage tab

Answer 6

How I can move table to another filegroup?

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

Step 1 :

Check on which filegroup table is residing:

-- 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 an existing table / tables to the new Filegroup

If the filegroup you want to move the table to doesn’t already exist then please create the secondary filegroup and then move the table.

To move a table to a different filegroup involves moving the table’s clustered index to the new filegroup. The leaf level of the clustered index actually contains the table data. So moving the clustered index can be done in a single statement 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 Non-Clustered indexes to secondary filegroup

You have to move the Non-clustered indexes manually by using the below mentioned 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 a clustered index on the new filegroup and then drop it (if necessary).

Answer 7

In SSMS, expand Tables, expand the table you want to move, expand Indexes, right-click on the clustered index, click "Script Index as" -> "Drop and Create to"

This will open a query window with the script to drop the clustered index and create a new with the same specs as original one.

In the query window, at "ALTER TABLE <> ADD CONSTRAINT" statement, change the name of filegroup after "ON" keyword at the end of statement, e.g. if table is on PRIMARY filegroup and you want to move to filegroup with the name "SECONDARY", change "ON [PRIMARY]" to "ON [SECONDARY]".
If you want the table to remain online during the operation, please also change "ONLINE = OFF" to "ONLINE = ON".

Execute the script and it will drop the original and create a new one in given filegroup.

Answer 8

I think these steps are very simple and straight forward to move any table to a different file group (through Management Studio):

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

• Change your cluster index to non-cluster and change its file group simply (like the previous step)

• Add a new temporary cluster index with "new file group" through this command(or via IDE) :

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

  (the above command causes to move all data to new file group)

• Delete the above temporary PK (when it does its job prefectly!)

• Change back your main cluster index to be cluster index again (through IDE again)

The benefit of above steps is not need to drop existing FK relationships. Also using IDE prevents losing data in error conditions.

NOTE : be sure Disk Quota is not enabled for your FileGroup or set it ocrrectly. Otherwise you get you "filegroup is full" exception!

Answer 9

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

Answer 10

Had the same problem and this is the script I came up with (tested, and works just as you would expect):

DECLARE @Target_Filegroup sysname = N'XXX';
-----------------------------------------------------------------------------------------
;WITH [IX] AS(
SELECT
[Schema]                 = SCHEMA_NAME(so.[schema_id]) COLLATE DATABASE_DEFAULT,
[Object_Name]            = so.[name] COLLATE DATABASE_DEFAULT,
[Object_Type]            = so.[type],
[Is_Published]           = so.[is_published],
[Is_Schema_Published]    = so.[is_schema_published],
[IX_Name]                = ix.[name] COLLATE DATABASE_DEFAULT,
[IX_Type]                = ix.[type],
[IX_Type_Desc]           = ix.[type_desc] COLLATE DATABASE_DEFAULT,
[Is_PK]                  = ix.[is_primary_key],
[Is_Unique]              = ix.[is_unique],
[IX_Data_Space]          = ds.[name] COLLATE DATABASE_DEFAULT,
[Is_UC]                  = ix.[is_unique_constraint],
[FF]                     = ix.[fill_factor],
[Is_Padded]              = ix.[is_padded],
[Is_Disabled]            = ix.[is_disabled],
[Is_Hypothetical]        = ix.[is_hypothetical],
[Allow_Row_Locks]        = ix.[allow_row_locks],
[Allow_Page_Locks]       = ix.[allow_page_locks],
[Has_Filter]             = ix.[has_filter],
[Filter]                 = ix.[filter_definition] COLLATE DATABASE_DEFAULT,
--[auto_created]         = ix.[auto_created],
--[optimize_seq_key]     = ix.[optimize_for_sequential_key],
[Indexed_Columns] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])))
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 0
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT,
[Indexed_Columns_Order] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])), CASE [is_descending_key] WHEN 1 THEN ' DESC' ELSE ' ASC' END)
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 0
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT,
[Included_Columns] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])))
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 1
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT
FROM sys.objects so
LEFT JOIN sys.indexes ix ON so.[object_id] = ix.[object_id]
LEFT JOIN sys.data_spaces ds ON ix.[data_space_id] = ds.[data_space_id]
WHERE so.[type] IN ('U', 'V')
AND so.[is_ms_shipped] = 0
AND ix.[type] IS NOT NULL --| so we get heaps, and indexed views 
)
SELECT
[Schema], [Object_Name], [Object_Type],
--[Is_Published], [Is_Schema_Published],
[IX_Name],
[IX_Data_Space],
[IX_Move_SQL] = CASE WHEN [IX_Data_Space] <> @Target_Filegroup AND [IX_Type] IN (1,2) THEN CONCAT(
                                                'CREATE ', CASE [Is_Unique] WHEN 1 THEN 'UNIQUE ' END, [IX_Type_Desc], ' INDEX ', QUOTENAME([IX_Name]),
                                                ' ON ', QUOTENAME([Schema]), '.', QUOTENAME([Object_Name]), ' (', [Indexed_Columns_Order], ')',
                                                CASE WHEN [Included_Columns] IS NOT NULL THEN CONCAT(' INCLUDE (', [Included_Columns], ')') END,
                                                CASE WHEN [Has_Filter] = 1 THEN CONCAT(' WHERE ', [Filter]) END,
                                                ' WITH (PAD_INDEX=', CASE [Is_Padded] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', FILLFACTOR=', CASE WHEN [FF] = 0 THEN '100' ELSE CAST([FF] as varchar(3)) COLLATE DATABASE_DEFAULT END,
                                                ', ALLOW_ROW_LOCKS=', CASE [Allow_Row_Locks] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', ALLOW_PAGE_LOCKS=', CASE [Allow_Page_Locks] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', DROP_EXISTING=ON ',')',
                                                ' ON ', QUOTENAME(@Target_Filegroup), ';')

                                                 END COLLATE DATABASE_DEFAULT
FROM [IX]
ORDER BY [Object_Type] ASC, [Schema] ASC , [Object_Name] ASC;