MS SQL Basics Tutorial: System Database

SQL Server 2000 has four systems databases (System Databases): Master, Model, Msdb, Tempdb. The files for these system databases are stored in the Data folder of the MSSQL subdirectories of Microsoft SQL Server (the default installation directory). The files contained in each system database are shown in table 6-1.

Note: If you do not see the system database in Enterprise Manager, you can right-click the server and choose Edit SQL Server Registration properties from the shortcut menu, and you will see the Edit Server Registration Properties dialog box shown in Figure 5-5, from which you can select Show the system databases and System objects option, and then refresh the Enterprise Manager interface.

6.9.1 Master Database

The Master database records all system information for a SQL Server system. These system information are mainly:

All login information, system setup information, SQL Server initialization information, information about other system databases and user databases in the system, including the location of their main files. 6.9.2 Model Database

The Model database is the creation template for all user databases and the tempdb database. When you create a database, the system copies the contents of the model database to the newly created database. Thus, using model database template characteristics, by changing the model database settings, and will often use the database objects copied to the model database, can greatly simplify the database and its object creation, Setup work, save a lot of time for users. In general, you can add the following content to the model database:

Minimum database capacity; database option settings; Frequently used database objects, such as user-defined data types, functions, rules, defaults, and so on. 6.9.3 MSDB Database

SQL Server, SQL Server Enterprise Manager, and SQL Server Agent use the msdb database to store planning information and information related to backup and recovery. In particular, SQL Server agents need to use it to perform operations such as scheduling work and alerting, recording operators, and so on.

6.9.4 Tempdb Database

The TEMPDB database is used as a temporary storage space for the system, and its main functions are:

Store user-established temporary tables and temporary stored procedures, store user-described global variable values, create temporary tables for data sorting, and store users ' data filtered by cursor descriptions. The presence in the tempdb database is not logged, so data operations on the tables in the tempdb database are much faster than in other databases.

Note: When you exit SQL Server, all objects created by the user in the tempdb database will be deleted. Every time SQL Server starts, the tempdb database is rebuilt, reverting to the initial state of the system setting. Therefore, you should never use the tempdb database as the final repository of data.

See the full set of "MS SQL Basics Tutorials"