Creating Filegroups

Filegroups 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 after 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 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

Transact-SQL

Enterprise Manager

SQL-DMO

To add a filegroup to a database

Transact-SQL

Enterprise Manager

SQL-DMO

See Also

Physical Database Files and Filegroups

sp_helpfilegroup

sp_helpfile

Using Files and Filegroups to Manage Database Growth