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

Can you move a table to new Filegroup/File without removing PK? Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 7:50 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:41 AM
Points: 114, Visits: 903
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
Post #1442743
Posted Tuesday, April 16, 2013 8:43 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:41 AM
Points: 114, Visits: 903
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
Post #1442782
Posted Tuesday, April 16, 2013 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 6,196, Visits: 13,354
Does the PK have a clustered index upon it at present?

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1442838
Posted Tuesday, April 16, 2013 11:02 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:41 AM
Points: 114, Visits: 903
Yes, it does.

Thanks.
Post #1442870
Posted Wednesday, April 17, 2013 12:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 6,196, Visits: 13,354
In that case you use CREATE INDEX specifying the new file group with the DROP_EXISTING = ON clause

See here for more detail


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1443105
Posted Wednesday, April 17, 2013 1:19 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:41 AM
Points: 114, Visits: 903
Thanks.
Post #1443467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse