A simple database might have one primary data file,
which is large enough to hold all data and objects, and one transaction log file.
A more complex database might have one primary data file,
five secondary data files, and two transaction log files.
How, then, could the data be spread across all the data files?
The answer is that filegroups can be used to arrange data.
Filegroups enable you to group files for administrative and data placement purposes.
Filegroups can improve database performance
by allowing a database to be created across multiple disks, multiple disk controllers, or RAID systems.
You can create tables and indexes on specific disks by using filegroups, thus enabling you to direct the I/O
for a certain table or index to specific physical disks, controllers, or arrays of disks.
There are three types of filegroups.
The main characteristics of these filegroups are outlined in the following list:
* Primary filegroup
Contains the primary data file and all other files not put into another filegroup.
System tables - which define the users, objects, and permissions for a database
- are allocated to the primary filegroup for that database.
SQL Server automatically creates the system tables when you create a database.
* User-defined filegroups
Includes any filegroups defined by the user during the process of creating (or later altering) the database.
A table or an index can be created for placement in a specific user-defined filegroup.
* Default filegroup
Holds all pages for tables and indexes that do not have a specified filegroup when they are created.
The default filegroup is, by default, the primary filegroup.
Members of the db_owner database role can switch the default status from one filegroup to another.
Only one filegroup at a time can be the default - again,
if no default filegroup is specified, the primary filegroup automatically remains the default.
The ALTER DATABASE command is used to change the default filegroup.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook