SQL Server 7.0 creates a database using a set of operating-system files, with a separate file for each database. Multiple databases no longer can exist in the same file. There are several important benefits to this simplification: Files can grow and shrink, and space management is simplified.
All data and objects in the database, such as tables, stored procedures, triggers, and views, are stored within the following operating-system files only.
| File | Description |
|---|---|
| Primary data file | This file is the starting point of the database. Every database has only one primary data file. The recommended file extension is .mdf. |
| Secondary data files | These files are optional and can hold all data and objects that are not on the primary data file. Some databases may not have any secondary data files, while others may have multiple secondary data files. The recommended file extension is .ndf. |
| Log files | These files hold all the transaction log information used to recover the database. Every database has at least one log file. The recommended file extension is .ldf. |
When a database is created, all the files that comprise the database are zeroed out (filled with zeros) to overwrite any data left on the disk by previously deleted files. Although this means that the files take longer to create, it prevents Windows NT from clearing out the files when data is written to the files for the first time (because they are already zeroed out) during normal database operations. This improves the performance of day-to-day operations.
A database consists of one or more data files and one or more log files. The data files can be grouped into user-defined filegroups. Tables and indexes then can be mapped to different filegroups to control data placement on physical disks.
Filegroups are a convenient unit of administration that improve flexibility. With a terabyte-size database, regardless of backup speed, it is impossible to back up the entire database within a reasonable window. SQL Server 7.0 allows you to back up a different portion of the database each night on a rotating schedule.
Filegroups work well for sophisticated users who know where they want to place indexes and tables. SQL Server 7.0 can work effectively without filegroups; therefore, many systems will not need to specify user-defined filegroups. In this case, all files are included in the default filegroup, and SQL Server 7.0 can allocate data effectively within the database.
Log files are never a part of a filegroup; log space is managed separately from data space.
Files and filegroups improve database performance by allowing a database to be created across multiple disks, multiple disk controllers, or redundant array of inexpensive disks (RAID) systems. For example, if your computer has four disks, you can create a database that comprises three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data simultaneously, which speeds up database operations.
Additionally, files and filegroups permit better data placement because a table can be created in a specific filegroup. Performance is improved because all I/O for a specific table can be directed at a specific disk. For example, a heavily used table in a database can be placed on one file in one filegroup located on one disk, and another less heavily accessed table can be placed on another file in another filegroup located on a second disk.
Here are some recommendations for files and filegroups: