Move table to different filegroup

Question

Move table to different filegroup

iadba

Say Hey Kid

Points: 692
More actions
September 26, 2003 at 3:01 pm

#59844

I know you can move a table to a different filegroup in Enterprise Manager. How can this be done via a sql statement? I have numerous tables to move and doing so through EM will take forever.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

quote:
I know you can move a table to a different filegroup in Enterprise Manager. How can this be done via a sql statement? I have numerous tables to move and doing so through EM will take forever.

Recreate the clustered index; if you use the DROP_EXISTING option, it will be faster:

CREATE CLUSTERED INDEX Employee_Ind ON Employee(LName,FName,Minit)

WITH DROP_EXISTING

ON SECONDARY

--Jonathan

iadba Say Hey Kid Points: 692 More actions · Answer 2

What if there is no clustered index on the table?? Is there any other way to accomplish this?

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
What if there is no clustered index on the table?? Is there any other way to accomplish this?

Create a clustered index on the desired filegroup. Then drop that index.

--Jonathan

iadba Say Hey Kid Points: 692 More actions · Answer 4

Is there no other way?? I've got 170 tables and 108 indexes to move. Creating a dummy index and then dropping it will take just as long, if not longer, than doing the moves via EM.

BrenBart SSCrazy Points: 2622 More actions · Answer 5

It seems kind of ass-backwards but you could create blank tables of the structure needed in the filegroup needed with a different name then DTS the data into them, rename the old tables and rename the new ones.

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"

(Memoirs of a geek)

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

BrenBart SSCrazy Points: 2622 More actions · Answer 6

It might also be interesting to see a profile trace of SQL Server moving a table from one filegroup to another.

I suppose a simpler method if you have the drive space would be to create a complete DDL of the database, alter the script with the new filegroup info, create the database and DTS all the data into it.

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"

(Memoirs of a geek)

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

quote:
Is there no other way?? I've got 170 tables and 108 indexes to move. Creating a dummy index and then dropping it will take just as long, if not longer, than doing the moves via EM.

Are you talking about your time or the server's time? If you mean the time it takes you to do this, that could be eased by just writing a script with a cursor. If you mean the time that the server takes to complete each "copy," that would probably be faster if you did it through QA.

Couple of points:

It's a good idea to have a clustered index on each table. There are still technical glitches with heaps, and I've not seen many schemas that didn't benefit from the correct clustered indexes.
It should be unusual to need multiple filegroups in these days of terabyte-size array volumes. Although you can still see recommendations for optimizing a SQL Server system by placing tables and/or indexes on different physical arrays by using filegroups, you will actually get better performance by just striping all the drives into one array with one filegroup.

--Jonathan

Edited by - jonathan on 10/06/2003 3:27:10 PM

--Jonathan

iadba Say Hey Kid Points: 692 More actions · Answer 8

Time wise...I'm referring to both. This system is 24x7 and downtime needs to be held to a absolute minimum. As for adding clustered indexes, I can suggest it, but cannot just implement them as it is a vendor application and any modifications to the database schema must come from the vendor.

Jonathan SSC-Insane Points: 20427 More actions · Answer 9

quote:
Time wise...I'm referring to both. This system is 24x7 and downtime needs to be held to a absolute minimum. As for adding clustered indexes, I can suggest it, but cannot just implement them as it is a vendor application and any modifications to the database schema must come from the vendor.

If tempdb is on a different physical array, and has space for this, the index creation will not take as long if you use the WITH SORT_IN_TEMPDB option, particularly if the heaps have nonclustered indexes.

I cannot imagine that all tables must be moved together, so perhaps you could perform the operations on some of the smaller tables to gauge how long they are locked, and then develop the "downtime" strategy for the larger tables.

--Jonathan

Omri Bahat-228920 Mr or Mrs. 500 Points: 540 More actions · Answer 10

I wrote a script that reliably moves a table to another filegroup.

You can find it on

http://education.sqlfarms.com/ShowPost.aspx?PostID=59

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

Omri Bahat

SQL Farms Solutions

http://www.sqlfarms.com

Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

DALE-379196 Newbie Points: 5 More actions · Answer 11

With the greatest respect, as with most storage management, SQL Server is basic in comparison to Oracle:

ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME

Chris_P SSCommitted Points: 1753 More actions · Answer 12

Omri,

Your link is broken.

All interested,

I profiled doing this through EM, it creates a new temp table in the new tablespace, populates it, drops the old table and then renames the new (temp) table to the name of the former.

I found this article (http://decipherinfosys.wordpress.com/2007/08/14/moving-tables-to-a-different-filegroup-in-sql-2005/) which outlines how to move the clustered indexes (and tables via code.)

Chris.

James Reed Old Hand Points: 301 More actions · Answer 13

Omri script works great. I have used it a couple of time.

flemmingloth@hotmail.com Valued Member Points: 53 More actions · Answer 14

Which is faster when moving a table to new filegroup: (table wheighs 400GB)

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

--[1]

sp_rename 'TAB1' , 'TAB1_OLD' -- located on OLD_FG

GO

CREATE TABLE TAB1

( [primaryKey] [int] IDENTITY(1,1) NOT NULL,

[description] [varchar](2000) NULL,

[id] [varchar](15) NULL,

[name] [varchar](500) NULL,

[length] [int] NULL,

[bulkData] [image] NULL,

[timestamp] [varchar](20) NULL)

ON [new_FG] TEXTIMAGE_ON [new_FG]

GO

INSERT INTO TAB1 ()

SELECT

FROM TAB1_OLD

GO

DROP TABLE TAB1_OLD

GO

CREATE CLUSTERED INDEX [CIX_TAB1_id] ON TAB1([id]) ON [new_FG]

GO

ALTER TABLE TAB1 ADD CONSTRAINT [PK_TAB1] PRIMARY KEY NONCLUSTERED ([primaryKey]) ON [new_FG]

GO

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

or

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

--[2]

DROP INDEX [CIX_TAB1_id]

GO

ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO [new_FG])

GO

CREATE CLUSTERED INDEX [CIX_TAB1_id] ON TAB1([id]) ON [new_FG]

GO

ALTER TABLE TAB1 ADD CONSTRAINT [PK_TAB1] PRIMARY KEY NONCLUSTERED ([primaryKey]) ON [new_FG]

GO