System Databases in SQL Server

In this blog you will learn Master, Model, MSDB, TempDB in SQL Server.

SqlServer consists of 4 system databases:

1. Master
2. Model
3. MSDB
4. TempDB

Figure 1: System Databases

1. Master database is also called System database which consists of System objects( in other words system tables, views, etc).

This database is used by SQL Server internally to store the information of all the activities performed in SQL Server .

Note: A user should not use this database.

2. Model database as the name suggests, it is a database on which other databases will be created. It is like a template database.
Whenever a user creates a new database it will be created based on the model database.

3. MSDB: This database stores the information of certain tasks to be performed by SQL Server based on the users specification.

For Example:

• Taking backups of the database at regular intervals .
• Scheduled scripts which needs to be executed automatically.

4. Tempdb:

1. It is used internally by SqlServer whenever the queries are processed.
2. It represents the information for temporary period.

For Example: SELECT* from emp

Thank you