Creating Filegroups
A file cannot be a member of more than one filegroup. Tables, indexes, and text, ntext, and image data can be associated with a specific filegroup. This means that all their pages are allocated from the files in that filegroup.
There are three types of filegroups:
- Primary filegroup
This filegroup contains the primary data file and any other files not placed into another filegroup. All pages for the system tables are allocated from the primary filegroup.
- User-defined filegroup
This filegroup is any filegroup specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement, or on the Properties dialog box within SQL Server Enterprise Manager.
- Default filegroup
The default filegroup contains the pages for all tables and indexes that do not have a filegroup specified when they are created. In each database, only one filegroup at a time can be the default filegroup. If no default filegroup is specified, the default is the primary filegroup.
A maximum of 256 filegroups can be created for each database. Filegroups can contain only data files. Transaction log files cannot be part of a filegroup.
Note Filegroups cannot be created independently of database files. The filegroup is an administrative mechanism of grouping files within the database.
To add a filegroup when creating a database