Move tables to a new filegroup

Question

Post reply

Move tables to a new filegroup

shawnjohnson

SSCommitted

Points: 1551
More actions
February 9, 2012 at 2:01 pm

#264163

I'm working to move tables out of the now large, single file PRIMARY filegroup into other filegroup(s) with multiple, smaller files and was wondering if anyone could give pros and cons on moving data in the following two ways:
USE DBA_DB_COPY
GO
ALTER TABLE [dbo].[XXX] DROP CONSTRAINT XXX_PK WITH (MOVE TO FG_NEW) ;
GO
ALTER TABLE [dbo].[XXX] ADD CONSTRAINT XXX_PK PRIMARY KEY(RateKey) ;
GO
vs.
USE DBA_DB_COPY
GO
CREATE UNIQUE CLUSTERED INDEX XXX_PK
ON dbo.XXX(
[RateKey] ASC)
WITH (DROP_EXISTING = ON)
ON FG_NEW ;
GO
I've ran them both and they work well on my test copy of the DB, but wanted to see what you thought.
Thanks,
Shawn

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

My vote goes to CREATE CLUSTERED INDEX with DROP EXISTING.

It's the simplest way and it doesn't need additional steps.

Moreover, if you're on Enterprise Edition, you can do it ONLINE.

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

shawnjohnson (2/9/2012)
USE DBA_DB_COPY
GO
ALTER TABLE [dbo].[XXX] DROP CONSTRAINT XXX_PK WITH (MOVE TO FG_NEW) ;
GO
ALTER TABLE [dbo].[XXX] ADD CONSTRAINT XXX_PK PRIMARY KEY(RateKey) ;
GO

That leaves a small gap in which duplicate data can be added to the table and, if the primary key is clustered that rebuilds all nonclustered indexes twice (once in the drop, once in the create)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shawnjohnson SSCommitted Points: 1551 More actions · Answer 3

OK, thanks for the replies. I'll work with the DROP EXISTING and since I'm using Enterprise look at the ONLINE = ON as well.

shawnjohnson SSCommitted Points: 1551 More actions · Answer 4

I've been doing this well for the past month using the DROP_EXISTING = ON, but had a follow up question. Are there any special considerations for a table with BLOB data (varbinary(max) data type)? This table is a heap that I was going to make a clustered index on it using a column with int datatype and then drop the clustered index.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Yeah, the LOB data cannot be moved if it's stored out of row (if you have LOB_DATA allocation units). There is no way to relocate that, the only option is to create a new table with the TEXT_IMAGE on the desired filegroup and migrate the data into the new table, dropping the old once finished.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shawnjohnson SSCommitted Points: 1551 More actions · Answer 6

GilaMonster (3/14/2012)
Yeah, the LOB data cannot be moved if it's stored out of row (if you have LOB_DATA allocation units). There is no way to relocate that, the only option is to create a new table with the TEXT_IMAGE on the desired filegroup and migrate the data into the new table, dropping the old once finished.

I thought I had read that somewhere researching these moves, but couldn't remember where.

Thanks for the quick reply!