System-Supplied Databases

Here's a quick look at SQL Server 7.0's seven system-supplied databases.

7. tempdb

The tempdb database contains temporary tables and stored procedures that client applications use. Also, SQL Server uses this database in its own working tables. Don't use tempdb for permanent user objects because it's deleted and recreated each time SQL Server starts. The Tempdb.mdf and Templog.ldf physical files contain the tempdb database.

6. model

SQL Server uses the model database as a template for all databases created on the SQL Server system. When you use the CREATE DATABASE statement to create a new database, SQL Server copies the model database contents to the new database. SQL Server also uses the model database to create the tempdb database each time SQL Server starts. SQL Server uses the Model.mdf and Modellog.ldf physical files to implement the model database.

5. master

The master database tracks SQL Server system information. The master database records system configuration settings, the structure of all system and user databases, and login information. SQL Server also stores most system-supplied stored procedures in the master database (it stores the remainder in the msdb database). The Master.mdf and Mastlog.ldf physical files contain the master database.

4. msdb

The msdb database keeps track of jobs, alerts, and operators. In addition, the msdb database maintains the schedule of SQL Server Agent jobs. The Msdbdata.mdf and Msdblog.ldf files contain the msdb database.

3. distribution

SQL Server uses the distribution database as part of its transactional replication in order to store all database modifications made to the publishing database. The distribution database stores database transactions until they are forwarded to the subscribing databases. SQL Server uses the Distribution.mdf and Distribution.ldf files to implement the distribution database.

2. pubs

The venerable pubs database, modeled on a fictitious book company, is a simple sample database that all SQL Server versions include. Although you don't need the pubs database to operate SQL Server, tutorials and documentation often refer to the pubs database in examples. And at only 3MB, the database costs next to nothing to keep. The pubs.mdf and pubs_log.ldf files contain the pubs database.

1. Northwind

Originally included in Microsoft Access desktop database, the Northwind database contains sales data for a fictitious import and export company. The Northwind database is a welcome addition to SQL Server 7.0 because it supplies a sample order-entry type of database example that the simpler pubs example doesn't include. A more realistic example than pubs, the Northwind database is also bigger at 4MB. SQL Server uses the Northwnd.mdf and Northwnd.ldf files to implement the Northwind database.