Move indexes to new filegroup

If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
Regards,
Jamie
I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
Regards,
Jamie
"Tibor Karaszi" wrote:

> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>