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 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

The T-SQL equivalent of what we are trying to accomplish in this recipe is as follows:

CREATE UNIQUE CLUSTERED INDEX PK_Student 
ON dbo...