Need to place tables in separate physical files

Question

Need to place tables in separate physical files

ashishj

SSC Veteran

Points: 200
More actions
October 21, 2002 at 3:10 am

#64273

Hi,
I am using SQL Server 2000. I already have a large amount of data with me. Due to some performance issues I need to place some of the tables in separate physical files in the same filegroup. Can I do this? Will my data be lost? What is the procedure to this? Any help on this will be really appreciated.
Thanks in advance.
Ashish
Edited by - ashishj on 10/21/2002 03:13:47 AM
Edited by - ashishj on 10/21/2002 03:18:49 AM

Viewing 7 posts - 1 through 6 (of 6 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

You cannot seperate tables to seperate files within the same filegroup. To migrate them to seperate files they must be set to seperate filegroups. This does not affect the database in any other way as it is a storage level item. No data will be lost.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

ashishj SSC Veteran Points: 200 More actions · Answer 2

Hi,

I am new to this and I dont have any idea how to go about this. Can you give me the steps to do this OR any references where I can get more information to execute this?

Thanks in advance.

Ashish

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

You cannot just issue an alter table statement. You can change the filegroup in the enterprise manager, right click on the table and select design table.

This can do the change or you can generate the script and apply it using Query Analyser, this is good to learn what is being done. You create a new file group by right clicking on the database and add the file groups you want. You have to create the filegroups before you assign a file to the filegroup.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Unfortunately, there is a lot to do if you watch EM to make sure constraints, key referneces, and permissions stay in place. So using EM look at the table and get size from it (view taskpad) then right click on the database in EM in the tree view (left window) and select properties. On the second tab (Data files) and set the name path and filegroup name there, along with initial size (make sure at least as big as the table reported plus a a bit to provide room to place into it) and growth amounts, then press ok. Now expand the DB in the treeview and click on the table item under that branch. In the right window you will see the tables, right click the table and select "Design Table", and on the design window right click and choose properties. On the first tab "Tables" you will find the filegroup options at the bottom of the list of items (there is one for table and one for text, set both to the same is best unless you place your text columns data into a seperate filegroup by themselves, it has some advantages and disadvantages), then click OK and save the design changes. Depending on the size of your data the information will be populated to the new table location in a new table (it will be the old name when done), when all is completed the old table is deleted and the new renamed to the old name. Keep in mind that during this you will have two copies of the data so your drives need to be able to support this fact. Once the table is moved to the new filegroup you can srink the old one it cam from down to free that sapce if needed.

Hope that helps.

Note: as with any change in a database that is going to be drastic (or even minor), please make a full backup before begining and if able second full backup when done, for safety.

Edited by - antares686 on 10/21/2002 04:42:51 AM

ashishj SSC Veteran Points: 200 More actions · Answer 5

Hi All,

Thanks to all of you for the information. This helped me a lot.

Ashish

mdburr Default port Points: 1483 More actions · Answer 6

Incidentally, once you've created your new filegroup, there is an alternative for moving your table to the new filegroup. Simply create a clustered index on that table - your can create the clustered index on any column you like - and in the CREATE INDEX statement, specify that the index should be created on the new filegroup. Since the leaf-level pages of a clustered index are the table's data pages, this will have the effect of moving all of the data pages over to the new filegroup. This avoids the process that EM uses that creates a new table, copies the data to the new table, drops the old table, and renames the new table. It also avoids the need to recreate permissions and reestablish foreign key constraints.

Matthew Burr