18

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647

3 Answers3

25

Something like this should help:

CREATE UNIQUE CLUSTERED INDEX PK_YourTableName 
    ON dbo.YourTableName(YourPKFields)
    WITH (DROP_EXISTING = ON) ON [NewFilegroup]
Zanon
  • 29,231
  • 20
  • 113
  • 126
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • I think this worked. When i checked the table->properties->Storage it shows the 'FileGroup' as being my new filegroup (from above) but the Text filegroup == PRIMARY? is that right? or should it also be by secondary fielgroup (eg. NewFilegroup). ? – Pure.Krome Apr 03 '09 at 06:07
  • 3
    That's a different story - you can specify a filegroup for the Text/NText fields when you create the table, but Books Online says: "The storage of any large value column data specified in CREATE TABLE cannot be subsequently altered. " :-( – marc_s Apr 03 '09 at 20:27
  • Correct, you can't change the filegroup for TEXT/NTEXT data after the table has been created. You would need to move the data into a new table using the correct file groups, then rename that table to the old tables name to change the TEXT/NTEXT file group. – mrdenny Apr 06 '09 at 08:26
  • This is worked but when I checked the storage section in properties of table I saw Text filegroup is just remain on Primary. I created a table as below: "CREATE TABLE [dbo].Images( [id] [bigint] NOT NULL, [name] [nvarchar](500) NOT NULL, )ON SecondFG" and both Filegroup and Text filegroup now are SecondFG filegroup. Why this happend? – QMaster Jan 24 '15 at 19:34
7

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.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • >>Right-click any column, and then click Properties. Nope, no option to do that :( Screen shot? – Pure.Krome Apr 03 '09 at 03:53
  • must have chnaged in SQL 2008, the TSQL way should still work. I'll update when I find where the option moved to! – Mitch Wheat Apr 03 '09 at 04:09
  • hmm.... could you post the TSql please? I'm trying the following (and it's not working):- create index PK_TableFooId on dbo.NorthAmerican_Countries (id) WITH DROP_EXISTING ON SpatialDataReadOnly – Pure.Krome Apr 03 '09 at 04:18
  • updated to reflect how SQL 2008 does it via SSMS. Note restrictions on unique or primary key constraint. – Mitch Wheat Apr 03 '09 at 04:23
  • ah damn. that's my prob :( the table has only one index (the PK). can u edit your reply (one more time) to include some sample sql, please? – Pure.Krome Apr 03 '09 at 04:35
0

I agree with Marc. Rebuild cluster works well and is much faster than Tasks->Shrink Database->Files-> Empty file by migrating data to another file.

Creating indexes individualy allows controll over the load on the system without creating huge log files from select * into [your.new.table.] from .....

Himanshu
  • 31,810
  • 31
  • 111
  • 133
Guidence
  • 1