Files and Filegroups
Microsoft® SQL Server™ 2000 maps 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 within these 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.
Rules for Designing 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.