Click here to Skip to main content
14,304,265 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi All,
I have a small doubt.
I have a database called NORTHWIND in my Sql Server 2008. In the database i have table called Categories (which is a part of the primary file group). I want to move this table to a secondary file as i don't use this table often.
In what way can I achieve this requirement?
Posted
Comments
Amir Mahfoozi 1-Feb-12 1:26am
   
"move this table to a secondary file"
What do you mean ?
puja11191 1-Feb-12 1:42am
   
Actually when we create a new table it is added to the primary file (.mdf) by default. Now i want to move the table to a secondary file (.ndf) within the same database
   
File?!
--SA
puja11191 1-Feb-12 1:43am
   
I didn't get you??
Rajesh Anuhya 1-Feb-12 2:41am
   
Not a Clear Question
--RA
puja11191 1-Feb-12 5:09am
   
Sorry for the unclear question. Amir has given a solution kindly go through it. It will help you understand the query also
Rate this:
Please Sign up or sign in to vote.

Solution 1

In general, unless you are running really short on space or performance, you should not worry about moving this table out of your database.

If you, however, feel there is a requirement, you can always move this table to another 'backup' database and drop it from your present one.

Just use Select * Into and the entire table would be copied.
   
Comments
puja11191 1-Feb-12 1:38am
   
Hi Abhinav,
Actually what you have suggested is quite correct. But my point is that how can i use the secondary file, take it from educational point of view
Rate this:
Please Sign up or sign in to vote.

Solution 2

Open SSMS then right click on your table and select Design View then press F4 in design view to see the properties pane.

In Regular Data Space you can change file group of this table.

Of course you should have defined file groups for your database to see them in that list.

If you want to do that by TSQL then read these pages :
http://stackoverflow.com/questions/712449/how-do-i-move-a-table-to-a-particular-filegroup-in-ms-sql-2008/712673#712673[^]

http://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way[^]

Hope it helps.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100