To add a filegroup when creating a databaseFilegroups can be created when the database is first created or later when more files are added to the database. However, it is not possible to move files to a different filegroup once the files have been added to the database.
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 will be allocated from the files in that filegroup.
There are three types of filegroups:
These contain the primary data file and any other files that are not put into another filegroup. All pages for the system tables are allocated from the primary filegroup.
These are any filegroups that are specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement, or on the property page within SQL Server Enterprise Manager.
These contain 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 per database, and 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
To add a filegroup to a database
| Physical Database Files and Filegroups | sp_helpfilegroup |
| sp_helpfile | Using Files and Filegroups to Manage Database Growth |