Assigning Data Files to Filegroups

When the developers create the Eval database, SQL Server 7.0 also creates the primary filegroup for the database that contains the primary data file (usually the data file with the .mdf extension and always the first file in the Database files list on the General tab of the Eval Properties dialog box). You cannot modify the name of the primary filegroup and you cannot choose a different filegroup for the primary data file. The filegroup for the primary data file must be the PRIMARY filegroup. The diagram in Using Multiple Data Files in Eval shows the File group column.

If you do not specify a default filegroup, the primary filegroup is the default filegroup. You can also create additional filegroups and select another filegroup as the default filegroup for tables and indexes. The Eval database uses the primary filegroup as the default filegroup. You can add filegroups (except the primary filegroup) to a database at any time using Transact-SQL or the Properties dialog box for the database. When using the latter method, you must also add a data file; if you do not add a data file the filegroup is not added to the system tables (sysfilegroups) when you exit the dialog box.

When deciding whether to use filegroups, consider the following:

• Consider the number of disk drives the database will use. If the database is small and you are only using one disk drive, there are few benefits to implementing filegroups.
• Filegroups allow you to group data files for administrative purposes.
• Filegroups allow you to control the physical location of tables and indexes in your database.
• Filegroups can improve the performance of queries. When the different tables joined in a query are created on different filegroups, you can use parallel disk I/O searching for joined data.
• Filegroups have a proportional fill strategy. This means that SQL Server fills data files (primary and secondary) within a filegroup in proportion to their free space instead of writing to one data file until it is full. This allows you to distribute the data load.
• Using at least two filegroups provides a way for you to designate a default filegroup for user data and objects (located in secondary data files) and manage this data exclusive of system files, which are always located in the primary data file.

Note  To see a list of filegroups for a database, select the database and run the statement "SELECT * from sysfilegroups" from the SQL Server Query Analyzer. All filegroups are listed in the results pane and the filegroup whose status column contains the value 16 is the default filegroup. A database can have only one default filegroup at a time.