Solved

Filegroups?

Posted on 2002-07-16
6
1,145 Views
Last Modified: 2012-08-14
How do you mage filegroups in SQL Server?  Can you associate tables to filegroups?  I am used to Oracle and wondering if filegroups are szupposed to fill the same roll as tablespaces?
0
Comment
Question by:jmouland
6 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 7157710
Filegroups are *similar* to tablespaces, but not identical.

-- How do you ma[na]ge filegroups in SQL Server?

Filegroups are created with the CREATE DATABASE and ALTER DATABASE commands.  A filegroup in this context is just a name, with one or more associated physical DB files.  These can be on different drives.  An example:

CREATE DATABASE MyDatabase
ON PRIMARY
  ( NAME='MyDatabase_Main',
    FILENAME='c:\mssql7\data\MyDBMain.mdf',
    SIZE=10,
    MAXSIZE=20,
    FILEGROWTH=2),
FILEGROUP My_FileGroup1
  ( NAME = 'My_FileGroup1_Data1',
    FILENAME = 'd:\data\My_FileGroup1_Data1.ndf',
    SIZE = 20MB,
    MAXSIZE=100,
    FILEGROWTH=5),
  ( NAME = 'My_FileGroup1_Data2',
    FILENAME = 'e:\data\My_FileGroup1_Data2.ndf',
    SIZE = 20MB,
    MAXSIZE=100,
    FILEGROWTH=5)

-- Can you associate tables to filegroups?

Yes, individual tables can be associated with filegroups.  In fact, the table and the indexes of the table can each be associated with different filegroups.

0
 

Author Comment

by:jmouland
ID: 7157760
Ok I think i just got spoiled by SQL Server and thought u could use enterprise manager to do everything.  Thanks for the help.
0
 
LVL 9

Expert Comment

by:miron
ID: 7158843
SQL Server 2000 with SP 2 does have an option of creating table on a specific filgroup, if my memroy is correct.

Cheers
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Expert Comment

by:pinkman
ID: 7161110
in the enterprise manager , in the desgin table
look near the "save" sign , you'll see "table and index
properties" sign in which you can change table's file
group and it will move the table to the new file group.
you can add files groups in the database property
window , you'll find it by right clicking on the database
name.
and don't loose your faith in the enterprise manager !
0
 

Author Comment

by:jmouland
ID: 7161792
Can you be a bit more detailed in how exactly I can create and manage filegroups in Eneterprise Manager.  I right clicked on the database and didn't see anywhere to add a filegroup.  In the design are I did see where to change the filegroup for the table filegroup and text filegroup (what are these exactly?)  But I didn't see where to manage the filegroups for any other object like index's and such.
0
 

Accepted Solution

by:
pinkman earned 100 total points
ID: 7162650
o.k.
i tell you what i'm doing . maybe someone in microsoft
designed it to be used differently , but it is working
for me. first lets move table t1 from db db1 to a new
physical location.
1) right click on db1 and then left click "properties"
2)click on the filegroups tab
3)add a new file group just by writing in the second row(or the first empty one) a new name , i use "secondary"
4)press the ok button
3)open the window again (step 1)
4)click on the "data files" tab
5) add a new row , give it your own name(first col),
brose or write the location on the second col ,
choose the secondary file group on the last col( it
opens a combo box with all filegroups names)
for now you can leave all the rest as default (file grow
atc...)
6)click ok
7)open the design table for table t1
8)change its "table filegroup" to "secondary" ,click
close and then click save.( you found the right window
already..)
i don't know what is the text file group option but step
8 will move the table to its new location.
as for indexes : when you create an index or open
its propertis , you'll see a field "index filegroup"
or "file group" where you have a combo box which allow
you to select , in our case between "primary" and
"secondary" and it will move the index to the new location.
and so on create physical locations as much as you want

hope this is ok , one day i will probably
need some answers on Oracle so stay sharp...

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 90
PL/SQL query 14 50
Sql query 34 20
How many Lat/Long for a single UK Postcode 5 12
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now