Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do i move a table to a particular FileGroup in SQL Server 2008

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 ?

like image 569
Pure.Krome Avatar asked Apr 03 '09 03:04

Pure.Krome


People also ask

How do I move an index from one filegroup to another?

Right-click the index that you want to move and select Properties. Under Select a page, select Storage. Select the filegroup in which to move the index. If the table or index is partitioned, select the partition scheme in which to move the index.


2 Answers

Something like this should help:

CREATE UNIQUE CLUSTERED INDEX PK_YourTableName 
    ON dbo.YourTableName(YourPKFields)
    WITH (DROP_EXISTING = ON) ON [NewFilegroup]
like image 134
marc_s Avatar answered Sep 23 '22 00:09

marc_s


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.

like image 22
Mitch Wheat Avatar answered Sep 21 '22 00:09

Mitch Wheat