separate table in different filegroup

Question

separate table in different filegroup

sqlquery-101401

SSCarpal Tunnel

Points: 4951
More actions
June 6, 2005 at 7:07 am

#86477

can anybody help me out to move one of my big big existing table to different file group.

thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Recreate the clustered index on that new filegroup. To speed this up you can use WITH DROP EXISTING. See BOL "CREATE INDEX" for complete syntax.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 2

thanks Frank ,but there is any way to move my existing table to other file group?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Yes, Enterprise Manager uses another way. It will create an interims table, move data there, create target table and move the data from interims table to target table. Not terribly efficient, if you ask me.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 4

hmm ,thanks it means without a kind of interm/temp table i cannot simply move my table to different filegroup.

am i correct

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

When you recreate the clustered index on that new filegroup, you don't need an interims table.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 6

Great Frank ,Frank one more question for you , through query analyzer i can specify the filegroup while creating new table .

is there any way in enterprise manager also where we can specify the file group other than the default one while creating new table?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Yes, there is. But when you use Enterprise Manager, it will use the interim table method.

One method might be to have Enterprise Manager create the change script and save it to a file. You could edit that file and remove all unnecessary stuff and only leave the CREATE INDEX statement in there.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

...too quick. I would still schedule this for off-peak usage hours. Depending on how large your table actually is.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]