Can you move a table to new Filegroup/File without removing PK?

Question

Can you move a table to new Filegroup/File without removing PK?

DKlein

SSCrazy

Points: 2351
More actions
April 16, 2013 at 7:50 am

#273040

These are the steps I was going to take:
--step 1
ALTER TABLE Orders.dbo.tbl_OrdersArchive
DROP CONSTRAINT [PK_tbl_OrdersArchive] WITH (MOVE TO SECONDARY)
--step 2
ALTER TABLE Orders.dbo.tbl_OrdersArchive
ADD CONSTRAINT [PK_tbl_OrdersArchive] PRIMARY KEY CLUSTERED
( [OrdersID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [SECONDARY]
Can this be done without removing the Primary Key?
Thanks,
DK

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DKlein SSCrazy Points: 2351 More actions · Answer 1

Maybe I should have included this in the OP, but I am aware that I can do an insert/select to a temp table and then rename, but I am worried about missing transations, and putting the database in a single user mode would render the same affect as using the Drop/recreate PK route, i.e... users not able to carry on business operations while this is taking place.

I also came across the "WITH (DROP_EXISTING=ON, ONLINE=ON)" but I can not use Online=On option because one of the columns in the table has xml. Plus, the PK issue.

Thanks,

David

Perry Whittle SSC Guru Points: 233836 More actions · Answer 2

Does the PK have a clustered index upon it at present?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

DKlein SSCrazy Points: 2351 More actions · Answer 3

DKlein

SSCrazy

Points: 2351

April 16, 2013 at 11:02 am

#1606927

Yes, it does.

Thanks.

Perry Whittle SSC Guru Points: 233836 More actions · Answer 4

In that case you use CREATE INDEX specifying the new file group with the DROP_EXISTING = ON clause

See here for more detail