<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Raj Gujar
»
Moving Large Table to Different File Group
29 posts, Page 1 of 3
1
2
3
»
»»
Moving Large Table to Different File Group
Rate Topic
Display Mode
Topic Options
Author
Message
Raj Gujar
Raj Gujar
Posted Wednesday, October 15, 2008 10:19 PM
Grasshopper
Group: General Forum Members
Last Login: Friday, January 04, 2013 2:54 PM
Points: 20,
Visits: 156
Comments posted to this topic are about the item
Moving Large Table to Different File Group
Post #586709
jayant-479472
jayant-479472
Posted Thursday, October 16, 2008 12:18 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, September 14, 2010 3:40 AM
Points: 2,
Visits: 39
how would we move a table without clustered index to a different filegroup ?
Post #586735
Anipaul
Anipaul
Posted Thursday, October 16, 2008 12:59 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 6:03 AM
Points: 4,787,
Visits: 1,335
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.
Post #586752
ChiragNS
ChiragNS
Posted Thursday, October 16, 2008 1:13 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365,
Visits: 1,825
Would Create Index WITH Drop_Existing work for moving non-clustered indexes.
"Keep Trying"
Post #586757
jayant-479472
jayant-479472
Posted Thursday, October 16, 2008 1:58 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, September 14, 2010 3:40 AM
Points: 2,
Visits: 39
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?
Post #586780
stuart-768223
stuart-768223
Posted Thursday, October 16, 2008 2:21 AM
Forum Newbie
Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 2,
Visits: 96
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.
Post #586783
Vitali Lisau
Vitali Lisau
Posted Thursday, October 16, 2008 5:44 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:48 PM
Points: 38,
Visits: 183
Clustered indexes causes page splits, and columns from it are included into any other index, so it adds overhead.
Post #586860
stuart-768223
stuart-768223
Posted Thursday, October 16, 2008 5:56 AM
Forum Newbie
Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 2,
Visits: 96
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!
Post #586865
Alexander Karmanov
Alexander Karmanov
Posted Thursday, October 16, 2008 6:19 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, April 17, 2013 8:44 AM
Points: 3,
Visits: 105
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/
Post Attachments
Tables moved to proper filegroups.sql.txt
(
181 views,
24.27 KB
)
Post #586875
Pieter-423357
Pieter-423357
Posted Thursday, October 16, 2008 6:47 AM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, March 12, 2013 1:26 PM
Points: 86,
Visits: 473
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.
Post #586903
« Prev Topic
|
Next Topic »
29 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
