to manage data files they need to be placed into "filegroups." By placing files
into filegroups, we can control how data is spread over multiple files and we
can also direct specific tables to specific files or sets of files.
Server allocates extents to tables, it does so by the proportional amount of the
free space in all files in a filegroup. For example, let's say we had a
filegroup that was made up of one file that had 50 MB free and another file that
had 100 MB free. For every extent that was allocated from the first file, two
extents would be allocated from the second file. Therefore the two data files
would fill up at approximately the same time.
Filegroups also allow control over what files a table will be stored in. For
example, say we had 4 hard disks with 1 secondary data file on each disk. In our
database let's say we have two large tables that we need to perform frequent
join operations on. In order to get the best performance we can setup two file
groups each containing two of the four data files. Next we can place each table
in one of the filegroups to give us maximum performance. Note that we will see
how to place tables and indexes in specific filegroups when we start creating
tables in an upcoming article.
a few specific filegroups we need to know about before we start creating our
database. First, the Primary data file is always created as part of the "Primary
filegroup" and it can't be removed from this group. The Primary filegroup
contains all the system objects (system objects are objects that store
information about the database) in the database. The system objects stored in
the Primary filegroup can't be removed from this group.
create a new object it is automatically placed into the "Default" filegroup
unless you specify otherwise. The Default filegroup can be set to any group you
wish, but by default, the Default filegroup is set to the Primary filegroup (got
that?). For example, if you created a new group called "MainStore" and set it as
the Default filegroup, any new tables you create would automatically be placed
into the "MainStore" filegroup.
come back to the concept of filegroups when we start creating objects. Also, I
will show you how to perform tasks such as moving objects between file groups
then. So, if you don't have a total grasp on how filegroups fit in...don't worry.
In a later article we will also take a more in-depth look at using files and
filegroups to optimize database performance.
Page 3: Creating a Database
See All Articles by Columnist Michael Aubert