Moving Tables Between File Groups

CREATE DATABASE TESTDB

USE TESTDB

Create table TestTable (Id Int identity primary key, Name Varchar(20))
Insert into TestTable (Name) Values ('SQL Server 2008')
go 2000

The below query displays file group name and data file associated to it. Initially it displays only one record  because there wont be any filegroup for LDF file

select fg.name as filegroup, (case when fg.is_read_only =0 then 'Write Mode' else 'Read Mode' end),
         f.name, f.physical_name
from sys.filegroups fg, sys.database_files f
where f.data_space_id = fg.data_space_id

ALTER DATABASE TESTDB ADD FILEGROUP SECONDARY_FG

ALTER DATABASE TESTDB
 ADD FILE (NAME = 'Second_File',
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\SecondaryFG.ndf')
     TO FILEGROUP SECONDARY_FG

ALTER DATABASE TESTDB ADD FILEGROUP THIRD_FG

ALTER DATABASE TESTDB
 ADD FILE (NAME='Third_File',
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\ThirdFG.ndf')
     TO FILEGROUP THIRD_FG
Create a NonClustered Index on Name Column

CREATE NONCLUSTERED INDEX IX_TestTable ON TestTable (Name)

-- Based on the below query we can identify the table and index stored along with file group information
Select so.name as Table_Name, sfg.name as FileGroupName, sc.name as ColumnName, si.name as Index_Name, si.type_desc as Index_Type
from sys.objects so, sys.indexes si, sys.columns sc, sys.index_columns sic, sys.filegroups sfg
where so.object_id = si.object_id and sc.object_id = so.object_id and sic.object_id = so.object_id
 and sic.column_id = sc.column_id and sic.index_id = si.index_id and si.data_space_id = sfg.data_space_id
and so.type_desc = 'USER_TABLE'

To Move Primary Key / Unique Clustered Index

When there is primary defined on the column automatically it creates a unique clustered index
Hence the base table is stored with primary key clusterd index, so moving the clustered index will moves the base table

CREATE UNIQUE CLUSTERED INDEX PX_TestTable ON TESTTABLE (ID) WITH (DROP_EXISTING = ON) ON [SECONDARY_FG]

Eventhough if you move Unique Clustered Index, Non-Clustered Indexes will not move automatically.
We need to move those manually using the below syntax.

CREATE NONCLUSTERED INDEX IX_TestTable ON TESTTABLE(ID) WITH (DROP_EXISTING = ON) ON [SECONDARY_FG];

Suppose if you have a Clustered Index then use the below syntax to move.

ALTER TABLE [dbo].[TestTable] DROP CONSTRAINT [PX_TestTable]

CREATE CLUSTERED INDEX PX_TestTable ON TESTTABLE (ID) ON [SECONDARY_FG]