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,
JamieUse 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...
> 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...
> > 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
>|||Try adding UNIQUE to the index definition, since it comes from a primary key constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
--
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:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...
> 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...
>> > 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
>>