June 13, 2013 at 8:05 am
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
June 13, 2013 at 9:27 am
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]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply