Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Move Primary Key Nonclustered Constraint and Clustered Index to New Filegroup? Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 8:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:17 AM
Points: 21, Visits: 270
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
Post #1463105
Posted Thursday, June 13, 2013 9:27 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:40 AM
Points: 141, Visits: 601
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

Post #1463155
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse