Files and Filegroups

Microsoft® SQL Server™ creates a database using a set of operating system files. All data and objects in the database, such as tables, stored procedures, triggers, and views, are stored only within the following operating system files:

• Primary

  This file contains the startup information for the database and is used to store data. Every database has one primary data file.

• Secondary

  These files hold all of the data that does not fit in the primary data file. If the primary file can hold all of the data in the database, databases do not need to have secondary data files. Some databases may be large enough to need multiple secondary data files or to use secondary files on separate disk drives to spread data across multiple disks.

• Transaction Log

  These files hold the log information used to recover the database. There must be at least one log file for each database.

For example, a simple database, sales, can be created with one primary file that contains all data and objects and a log file that contains the transaction log information. Alternatively, a more complex database, orders, can be created with one primary file and five secondary files; the data and objects within the database spread across all six files, and four additional log files contain the transaction log information.

Filegroups allow files to be grouped together for administrative and data allocation/placement purposes. For example, three files (data1.ndf, data2.ndf, and data3.ndf) can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks, thereby improving performance. The same performance improvement can be accomplished with a single file created on a RAID (redundant array of independent disks) stripe set. Files and filegroups, however, allow you to easily add new files on new disks. Additionally, if your database exceeds the maximum size for a single Microsoft Windows NT® file, you can use secondary data files to allow your database to continue to grow.

Rules of Files and Filegroups

Rules for designing files and filegroups include:

• A file or filegroup cannot be used by more than one database. For example, file sales.mdf and sales.ndf, which contain data and objects from the sales database, cannot be used by any other database.
• A file can be a member of only one filegroup.
• Data and transaction log information cannot be part of the same file or filegroup.
• Transaction log files are never part of any filegroups.

See Also