As mentioned, a SQL Server database is made up of a set of operating system files.
A database file can be either a data file or a log file.
Data files are used to store data and objects,
such as tables, indexes, views, triggers, and stored procedures.
There are two types of data files: primary and secondary.
Log files are used to store transaction log information only.
Log space is always managed separately from data space and can never be part of a data file.
Every database must be created with at least one data file and one log file,
and files may not be used by more than one database—that is, databases cannot share files.
The following list describes the three types of files that a database can use.
* Primary data file
A primary data file contains all of the startup information for the database
and its system tables and objects.
It points to the rest of the files created in the database.
It can also store user-defined tables and objects, but it is not required to do so.
Each database must have exactly one primary file.
The recommended file extension is .mdf.
* Secondary data files
Secondary data files are optional.
They can hold data and objects that are not in the primary file.
A database might not have any secondary files if all its data is placed in the primary file.
You can have zero, one, or multiple secondary files.
Some databases need multiple secondary files in order to spread data across separate disks.
The recommended file extension is .ndf.
* Transaction log files
A transaction log file holds all of the transaction log information used to recover the database.
Every database must have at least one log file and can have multiple log files.
The recommended file extension is .ldf.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook