Moving tables to different filegroups?

• Matthew Mullen

  SSC Enthusiast

  Points: 131

  More actions

  April 14, 2006 at 7:39 am

  #113679

  Sorry but I have tried to look this up and can not find anything.

  Problem: Someone here migrated a Access database into my SQL Server and I have noticed for a 10mb access created three datafiles on two filegroups.

  PDD.mdf  FILEGROUPRIMARY  --expected

  PDD.ndf   FILEGROUP:Tables     --Figures its access.

  PDD1.ndf   FILEGROUP:Tables    --Why?

  Well knowing SQL Server I used the DBCC SHRINKFILE (PDD1, EMPTYFILE) then used the ALTER DATABASE to delete the PDD1 file.

  That moved all the tables and indexes into the PDD.ndf on the Tables filegroup. My question is how do I move the Tables filegroup into my PDD.mdf file on my PRIMARY filegroup?

  I want to get rid of the PDD.ndf file.

  Thanks,

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  April 14, 2006 at 10:46 am

  #632665

  First, your current arrangement is actually part of a recommended practice:  put only system tables on the [PRIMARY] group, then create one or more additional filegroups for data tables and indexes, and mark one of them as default.  Since that wasn't your question, I won't yammer on about it.

  To answer your question:  to move a table from one filegroup to another, re-create the clustered index on the target filegroup:

  -- SQL 2000 syntax:
  CREATE CLUSTERED INDEX [name] ON [table](column, column, ...)
    WITH (DROP_EXISTING [,other options...]) 
      ON [target filegroup]

  -- SQL 2005 syntax (new syntax underlined):
  CREATE CLUSTERED INDEX [name] ON [table](column, column, ...)
    WITH (DROP_EXISTING = ON [,other options...]) 
      ON [target filegroup]

  ...then do the same to your non-clustered indexes.  By using the DROP_EXISTING option, you can execute CREATE INDEX statements against existing indexes without explicitly dropping them first.

  If any of your tables don't have clustered indexes, then one will be created for them when you do this.  Although this is also usually the preferred way to go (especially when choosing the right clustered key), you can always drop the clutered index after the move.

  -Eddie

  Eddie Wuerch
  MCM: SQL