Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Large Table to Different File Group


Moving Large Table to Different File Group

Author
Message
Raj Gujar
Raj Gujar
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 175
Comments posted to this topic are about the item Moving Large Table to Different File Group
jayant-479472
jayant-479472
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 41
how would we move a table without clustered index to a different filegroup ?
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6287 Visits: 1407
Yeah Jayant has a valid question for removing table without cluster Index.

If a table has non-clustered index then I believe you will remove them first and recreate them after moving the table into different space.



ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Would Create Index WITH Drop_Existing work for moving non-clustered indexes.

"Keep Trying"
jayant-479472
jayant-479472
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 41
I think my question is little confusing, let me make it more clear

If we have a table with cluster index then by simply recreating the clustered index on different file group will move the table also on that file group

now I have a table with no index (for simplicity) and I would like to move that table to a newly created filegroup, how would I do that?
sTTu
sTTu
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 151
jayant (10/16/2008)
I think my question is little confusing, let me make it more clear

If we have a table with cluster index then by simply recreating the clustered index on different file group will move the table also on that file group

now I have a table with no index (for simplicity) and I would like to move that table to a newly created filegroup, how would I do that?

Not sure I understand how not having a clustered index makes things simpler. Every table should have a clustered index. I would suggest creating a clustered index which would then move your data to the new filegroup.

If you really really don't want the index you can drop it again - however a clustered index does not add any overhead to the table so you should definitely have one.
Vitali Lisau
Vitali Lisau
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 183
Clustered indexes causes page splits, and columns from it are included into any other index, so it adds overhead.
sTTu
sTTu
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 151
Vitali Lisau (10/16/2008)
Clustered indexes causes page splits, and columns from it are included into any other index, so it adds overhead.


Ok yes granted, that is true, but will depend on the fill factor specified when creating the index.

In the context of the question I don't think it's relevant as [presumably] the table in question that is "simple" and doesn't need any indexes will have very few rows and is probably for lookup only, so page splits wouldn't be a factor.

For page splits to become an issue the table would have to have a reasonable number of inserts performed regularly and if that's that case, it should definitely be indexed!
Alexander Karmanov
Alexander Karmanov
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 134
Hi Raj,
Nice article. In the attachment there is a script that moves multiple tables in different databases at once. The original description of the script is here:
http://tsql.ca/Resources/Articles/MoveTablesbetweenFilegroups/tabid/610/Default.aspx

Unfortunately at the publishing time the some characters disappeared from the script, so I attached it here.

The idea was the same as yours - recreate the clustered index on another filegroup.
So, in my script (new) filegroups are assumed to be in place. All you need is to create a list of database/table/filegroup elements. Each table will be checked if it is in a proper filegroup, if not - it's moved to the proper one.

There are few more moments one should consider, especially if you plan to free and remove the original filegroup:
1. BLOB data is not moved this way, it resides in the original location. That means for the tables with BLOBs one has to create new table in the another filegroup(possibly specifying different filegroup for the BLOB data); copy the data over; drop original table and rename the new table.
2. Statistics is not moved this way. It should be detected by the table name and then dropped. When recreated - it will be placed in the same [new] filegroup as the table.

Such scenarios I handle in separate scripts delivered along with the attached one through my incremental build mechanism described here:
http://www.simple-talk.com/sql/database-administration/deploying-database-developments/
Attachments
Pieter-423357
Pieter-423357
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 568
To move a table to another filegroup, simply pick a (numeric) column, or create a new rowID column and create an clustered index on one of those columns.

The procedure is simple, however when the table becomes bigger you need to check your disk space and make sure that you have space equal to ~2 times the size of the table that you want to move or the operation will fail and you wasted a lot of time and resources.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search