Home Forums SQL Server 2008 SQL Server Newbies Move Primary Key Nonclustered Constraint and Clustered Index to New Filegroup? RE: Move Primary Key Nonclustered Constraint and Clustered Index to New Filegroup?

  • You can't directly transfer a primary key; you will need to drop it, then run the CREATE CLUSTERED INDEX statement with the DROP_EXISTING = ON clause and specify the new filegroup.

    After you've done that, recreate the non clustered primary key, again specifying the new filegroup.

    Bear in mind that if you have any LOB data in the table, that will still stay in the previously defined filegroup; I don't think it's possible to change that once it's been created, if you needed to you would have to create a complete new table in the correct filegroup to begin with, migrate the data in, and then drop your original table.

    ALTER TABLE [dbo].[system_xwalk_user_roles]

    DROP CONSTRAINT [PK_system_xwalk_user_roles]

    GO

    CREATE CLUSTERED INDEX [IX_system_xwalk_user_roles] ON [dbo].[system_xwalk_user_roles]

    (

    [UserRecID] ASC,

    [RoleRecID] ASC

    )WITH (DROP_EXISTING = ON) ON [MyNewFilegroup]

    GO

    ALTER TABLE [dbo].[system_xwalk_user_roles]

    ADD CONSTRAINT [PK_system_xwalk_user_roles] PRIMARY KEY NONCLUSTERED ([RecID] ASC) ON [MyNewFileGroup]

    GO

    Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]