SQL Server - Types of System Databases and Tables
Tags: sqlserver, sql, database, beginners
Published: 2023-11-09 18:15:12

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zifld9udlrso9lc40tso.png)



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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0oqpxieav90144mb6jqr.png)


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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rahyxong7tfklejnmm3v.png)


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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ta9cgv2nfqf1ncstgvia.png)

**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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ck7k46fpteid5vuf42zv.png)


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
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pj2z08fpxcrayqi2owsa.png)


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
)
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fmwikbm02gbxk4fn0rb4.png)


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
)
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a41pmnpphebg9xwh0xxq.png)


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`


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/60562m3uptf2reuhbh37.png)