Sql Server 2008 Query

Question

2.33/5 (3 votes)

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 31-Jan-12 19:17pm

puja11191

Add a Solution

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

Sergey Alexandrovich Kryukov 1-Feb-12 1:31am

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

2 solutions

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.

Posted 31-Jan-12 19:27pm

Abhinav S

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amir Mahfoozi · Accepted Answer · 2012-01-31T20:08:00

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.