In Microsoft SQL Server, there are several different types of databases that can be created and used for different purposes. Here is a brief overview of some of the most common types of databases in SQL Server and the scenarios in which they are typically used.
Databases are categorized into two different types, User and System:
User databases: These are the most common type of databases in SQL Server and are used for storing user data. They are created and managed by users and can be used for a wide range of applications, from simple data storage to complex data warehousing.
System databases: These databases are created and managed by SQL Server, and are used to store internal data and metadata, such as system-level security information, configuration settings, and other information required by SQL Server to function. The system databases are Master, Model, MSDB and TempDB.
The system databases are described by the following:
Master Database: The master database is the foundation of SQL Server, and stores all the system-level information and metadata required to manage a SQL Server instance, such as login accounts, system configurations, and other system-level objects.
Model Database: The model database is used as a template for creating new user databases. When a new user database is created, the SQL Server copies the data and objects from the model database to the new database.
MSDB Database: The MSDB database is used by SQL Server Agent for scheduling alerts and jobs, and by other features such as Service Broker, Database Mail, and SQL Server Management Studio.
TempDB Database: The TempDB database is used for temporary storage of data and objects, such as temporary tables, table variables, and intermediate query results. The TempDB is also used for version store, which is used for versioning data.