Moving an index to a different filegroup

This recipe illustrates how to move indexes to a different filegroup.

Getting ready

Using the TestDB database, or any database of your choice, let's create a table called Student with a clustered primary key.

Open SQL Server Management Studio and execute the following code:

USE TestDB
GO

-- this table is going to be stored to
-- the default filegroup
IF OBJECT_ID('Student') IS NOT NULL
DROP TABLE Student
GO
CREATE TABLE Student
(
ID INT IDENTITY(1,1) NOT NULL,
FName VARCHAR(50),
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO
-- insert some sample data
-- nothing fancy, every student will be called Joe for now :)
INSERT INTO Student(FName)
VALUES('Joe')
GO 20
INSERT INTO Student(FName)
SELECT FName FROM Student
GO 10

-- check how many records are inserted
-- this should give 20480
SELECT COUNT(*) FROM Student

We want to move the table to a different filegroup called FGStudent. Feel free to replace this with your choice of filegroup. Note...