Tanubhrt.This.Side

An instance of the server contains one or more databases. Each database has one or more schemas. Each schema has one or more database objects like tables, views, stored procedures etc.

The database in SQL server consists of tables containing rows and columns used to store structured data. Each column contains data such as name, roll no etc.

Types of SQL Databases –

•        User-defined databases
•         System databases

System Databases -: System databases are the ones that are made available during the installation of the system. They are used by the SQL server to perform various operations.  They include –

•          Master Database
•         Resource Database
•       TempDB Database
•        MSDB database
•       Model Database

Master Database – This is the database that contains system level information for the SQL server like configuration files and log on settings. It is used to create any databases. The SQL server will not work without the master database.

Model Database – Model database is used as the template for creating databases. When you fire the query, create database< database name>, a database is created by copying the contents of the model database. If any changes are made in the model database, they are reflected in the newly created databases.

Resource Database – Resource database is the database that contains all the system objects required in the SQL server. The SQL server sys objects physically persist in the resource database, however, they are logically present in the schema of every database. The database does not contain user data or Meta data. Up gradation is made easier by resource database as we can just copy the content of the resource database instead of copying and cresting objects.

MSDB Database – MSDB database is used for scheduling jobs such as auto backup of data every day at 12:00 am, or giving alerts when the stock prices go down below a certain level. It also includes sending messages to the manager regarding the quality of the product.

TempDB Database – TempDb database is used to store objects like tables and stored procedures temporarily. The objects created in the TempDB exists till the connection is lost. The SQL server database engine makes use of the TempDB to store objects of internal functions temporarily. The tempDB is recreated every time SQL server is started so that the system always starts with a clean copy of the database.

User-defined Databases – These databases are created by the user to achieve the functionality they want to, using the system databases.

Happy Learning 🙂