Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Moving Large Table to Different File Group Expand / Collapse
Author
Message
Posted Wednesday, October 15, 2008 10:19 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:48 AM
Points: 21, Visits: 165
Comments posted to this topic are about the item Moving Large Table to Different File Group
Post #586709
Posted Thursday, October 16, 2008 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:05 AM
Points: 2, Visits: 41
how would we move a table without clustered index to a different filegroup ?
Post #586735
Posted Thursday, October 16, 2008 12:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 5,343, Visits: 1,388
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
Posted Thursday, October 16, 2008 1:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
Would Create Index WITH Drop_Existing work for moving non-clustered indexes.

"Keep Trying"
Post #586757
Posted Thursday, October 16, 2008 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:05 AM
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?
Post #586780
Posted Thursday, October 16, 2008 2:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:27 AM
Points: 6, Visits: 114
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
Posted Thursday, October 16, 2008 5:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, October 16, 2008 5:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:27 AM
Points: 6, Visits: 114
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
Posted Thursday, October 16, 2008 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 1:58 PM
Points: 3, Visits: 117
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 (225 views, 24.27 KB)
Post #586875
Posted Thursday, October 16, 2008 6:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:55 AM
Points: 86, Visits: 478
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse