SQL Server - Types of System Databases and Tables

In this post, we will see five types of the System Databases and five types of User Tables that belong to SQL Server.

Types of System Databases:
1) Master database
Master database stores information regarding logon and server configuration. It contains information about every database that belongs to SQL Server.

Master database is the backbone of the SQL Server and SQL Server cannot run without it.

2) Model database
Model database is a template for every database that will be newly created. Model database stores the properties which will be copied to a newly created database. If you want certain properties your way other than default, then you should make change in the model database. Once a new database is created, it will be the same copy of the model database.

3) MSDB database
MSDB contains information that is required to run jobs, scheduler, alerts, database mail, service broker.

MSDB database also contains backup and restore history.

4) Resource database
The resource database is a hidden database that cannot be modified. It holds information regarding all system objects. All the 'sys' schema objects actually belong to resource database however they appear in every database.

5) TempDB database
TempDB database holds temporary tables namely local and global temporary tables, table variables. We will see more about temporary tables and table variables below.

Types of Tables:
1) System table
System tables belong to the System database that SQL Server uses for internal purposes. Usually, configuration or history related information is stored in these tables. Users cannot perform operations on these tables directly.

2) Regular table
Regular tables are also known as User tables since the user creates them under user database for storing information.

CREATE TABLE [dbo].[MyTable](
    [EmployeeId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Department] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

3) Local temporary table
Local temporary tables are stored in TempDB. Local temporary tables are visible to the user who has created those. They are deleted when the user disconnects the database instance.
Usually, local temporary tables are created programmatically in stored procedure and used for storing temporary data. It is good practice to programmatically drop temporary table after its use.

CREATE TABLE #MyLocalTempTable
(
EmpName VARCHAR(50),
EmpId INT
)

4) Global temporary table
Global temporary tables are also stored in TempDB. Global temporary tables are visible across all the users regardless of who has created those.
Global temporary tables are also created programmatically for storing temporary data. Notice that it uses ## as compared to single # for Local Temp Table.

CREATE TABLE ##MyGlobalTempTable
(
EmpName VARCHAR(50),
EmpId INT
)

5) Table variable
Table variable acts like a declared local variable. Table variables are automatically cleaned up by the SQL Server once the execution of the function or stored procedure is completed. If there are relatively fewer or limited rows in the picture that need to be stored temporarily then the Table variable is a suitable option.

DECLARE @MonthsOfTheYear TABLE([MonthNumber] INT, [MonthName] VARCHAR(20))

INSERT INTO @MonthsOfTheYear
VALUES 
(1, 'January'),
(2, 'February'),
(3, 'March'),
(4, 'April'),
(5, 'May'),
(6, 'June'),
(7, 'July'),    
(8, 'August'),
(9, 'September'),
(10, 'October'),
(11, 'November'),
(12, 'December')

You can query table as follows:
SELECT * FROM @MonthsOfTheYear