Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 move table to different filegroup

Author  Topic 

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-09 : 19:14:47
SQl experts and gurus:

How to move tables to the new filegroup which i created. suppose my database has only primary FG and after that i added one FG and due to performance reasons i want to move the existing table to NEW FG.
Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts
Posted - 2007-12-09 : 20:11:16
Recreate clustered index on new file group, or change file group in table design page.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 09:32:36
Thanks
Is there anyway without creating new clustered index. It is production server so primary key serves as a clustered index .
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 11:20:59
If you don't want to move the index, then you'll need to take some downtime to move the tables over.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 11:29:18
Can you please explain how can i do that?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 11:40:05
You'll need to recreate the tables by doing DROP/CREATE. In the CREATE TABLE statement, you specify the filegroup in the ON portion of the command. Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 11:42:23
quote:
Originally posted by tkizer

You'll need to recreate the tables by doing DROP/CREATE. In the CREATE TABLE statement, you specify the filegroup in the ON portion of the command. Check BOL for details.

I know that Tara but my questions is how can i move my existing tables from (e.g FGA to FGB) because it is production server i don't want to Drop/create.
Thanks Tara

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 11:56:09
The procedure is still the same. You will need to do this work during a maintenance window where you can take production down.

Do you even know if you'll get a performance boost by moving things to a different filegroup. Is this filegroup on a different disk than the primary one?

Why don't you look into the poor performing queries first?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 12:01:08
Yes Tara, this FG is on new Disk. This table is taking almost 60% of database size.So i want to move it to Different disk . So I want to know how can i do that. I searched everwhere but couldn't find the right one so i thought u experts knows.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 12:35:43
I've already told you how to do it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 12:42:45
IS there nothing like:
ALter table blah.....move to FG1 ..
or like that.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 12:45:45
DROP/CREATE

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2007-12-10 : 12:50:48
I'm not sure if this would work for you, but a variation on what Tara said is to create a new table on the new FG. Then insert the data from the "Old" table into the "New" table. And use "sp_rename" to rename the "New" table to have the same name as the "Old" talbe.

Obviously, there are some missing steps: like dropping the "Old" table or renaming it as a backup until you can verify that the new table is working correctly, indexing, etc.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 12:52:57
Yes all of those steps are required to save the production data, but my point is that you can't just alter the table. You have to specify the filegroup at the create table statement part.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 12:58:44
Thanks

But the database is almost 1TB and this table is 60% of it. And i am also thinking to do table partition for this table for the performances reason. So i was wondering how do i proceed.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 13:02:31
I'd look into partitioned views. Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 14:53:39
I think you meant partitioned table..What partitioned views has to do with this issue.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 15:04:29
No, I meant partitioned views. Please check out BOL for the differences between partitioned tables and partitioned views.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 15:17:10
I thought Partitioned table is a new feature in SQL 2005 and it is better than partitioned views
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-10 : 15:23:23
Yes it is a new feature in SQL Server 2005.

Partitioned views and partitioned tables are different things. You can't say one is better than the other. I will not be explaining the differences here as BOL already does this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2007-12-10 : 15:36:13
Since you are SQL Goddess(MVP) how was your experiences with Partitioned Views.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site