SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Move Primary Key Nonclustered Constraint and Clustered Index to New Filegroup?


Move Primary Key Nonclustered Constraint and Clustered Index to New Filegroup?

Author
Message
azenon
azenon
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 301
New to SQL and want to try my hand at moving tables/indexes to different filegroups. Can someone tell me the correct process for migrating the nonclustered primary key index and clustered index? I included the table script below.

CREATE TABLE [dbo].[system_xwalk_user_roles](
[RecID] [int] IDENTITY(1,1) NOT NULL,
[UserRecID] [int] NOT NULL,
[RoleRecID] [int] NOT NULL,
[IsSuspended] [bit] NOT NULL,
CONSTRAINT [PK_system_xwalk_user_roles] PRIMARY KEY NONCLUSTERED
(
[RecID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_system_xwalk_user_roles] ON [dbo].[system_xwalk_user_roles]
(
[UserRecID] ASC,
[RoleRecID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Default [DF_system_xwalk_user_roles_IsSuspended] Script Date: 06/13/2013 09:32:06 ******/
ALTER TABLE [dbo].[system_xwalk_user_roles] ADD CONSTRAINT [DF_system_xwalk_user_roles_IsSuspended] DEFAULT (0) FOR [IsSuspended]
GO
Matthew Darwin
Matthew Darwin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1199 Visits: 878
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search