How do i move a table to a particular FileGroup in MS Sql 2008

Question

Hi folks,

i've just created a secondary filegroup and wish to move some tables over to it and then make it read-only.

I'm not sure how to do this?

do i just use the ALTER blah blah TO MyFileGroup ?

Answer 1

There are two ways; one from SSMS and the other using TSQL.

From SQL Server 2008 Books Online:

To move an existing index to a different filegroup or partition scheme

• In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

• Expand Databases, expand the database that contains the table with the specific index, and then expand Tables.

• Expand the table in which the index belongs and then expand Indexes.

• Right-click the index to be moved and then select Properties.

• On the Index Properties dialog box, select the Storage page.

• Select the filegroup in which to move the index.

You cannot move indexes created using a unique or primary key constraint by using the Index Properties dialog box. To move these indexes, you need to drop the constraint using ALTER TABLE (Transact-SQL) with the DROP CONSTRAINT option and then re-create the constraint on the desired filegroup using ALTER TABLE (Transact-SQL) with the ADD CONSTRAINT option.

If the table or index is partitioned, select the partition scheme in which to move the index.

[Pre SQL Server 2008: To place an existing table on a different filegroup (SSMS)

• Expand Databases, expand the database containing table, and then click Tables.
• In the Details pane, right-click the table, and then click Design Table.
• Right-click any column, and then click Properties.
• On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.
• Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and text columns. ]

A more effective way is

to create a clustered index on the table. If the table already has a clustered index, you can use the CREATE INDEX command's WITH DROP_EXISTING clause to recreate the clustered index and move it to a particular filegroup. When a table has a clustered index, the leaf level of the index and the data pages of the table essentially become one and the same. The table must exist where the clustered index exists, so if you create or recreate a clustered index—placing the index on a particular filegroup—you're moving the table to the new filegroup as well.

Credit: Brian Moran

You can create (or recreate) the clustered index specifying the new filegroup for the ON clause and that will move the table (data). But if you have nonclustered indexes and want them on the other filegroup you must drop and recreate them also specifying the new filegroup in the ON clause. Ref.

Answer 2

Something like this should help:

CREATE UNIQUE CLUSTERED INDEX PK_YourTableName 
    ON dbo.YourTableName(YourPKFields)
    WITH (DROP_EXISTING = ON) ON [NewFilegroup]

Marc