Databases in SQL Server

There are basically the following two types of databases in SQL Server:

System Databases.
User Databases.

Introduction

In this lesson, we will delve deeper into the concept of databases within the context of SQL Server. We will cover the types of databases in SQL Server, how to create and manage them, and understand the basic structure and components that make up a SQL Server database.

Types of Databases in SQL Server

System Databases

SQL Server includes several system databases that are used to manage the server and the databases that reside on the server. These include:

1. master: This database records all the system-level information for SQL Server. It includes instance-wide metadata such as logon accounts, endpoints, linked servers, and configuration settings.

2. model: This database is used as a template for all new databases created on the SQL Server instance. Any modifications made to the model database will be applied to new databases.

3. msdb: This database is used by SQL Server Agent for scheduling alerts and jobs, and for storing information regarding backups and other maintenance tasks.

4. tempdb: This database holds all the temporary tables, temporary stored procedures, and other temporary working storage needs. It is recreated every time SQL Server is restarted.

5. Resource database: It is a read-only database that contains all the system objects included with SQL Server. Users cannot directly access or modify this database.

User Databases

User databases are created by users (DBAs, developers, etc.) to store data, create tables, views, stored procedures, and other objects.

Creating a Database in SQL Server

To create a database, you can use SQL Server Management Studio (SSMS) or execute a T-SQL script. Here’s an example using T-SQL:

CREATE DATABASE MySampleDB;

This SQL script creates a new database called MySampleDB with default settings.

Database Files and Filegroups in SQL Server

Each SQL Server database has two types of files:

1. Data Files: These files store the actual data and objects such as tables, indexes, stored procedures, etc. The primary data file has an extension `.mdf` and secondary data files have `.ndf` extensions.

2. Log Files: These files store the log information used to recover the database. Log files have the `.ldf` extension.

Filegroups

Filegroups allow you to organize data files in a database. There is always a primary filegroup, and you can create additional secondary filegroups. This is useful for administrative tasks such as backup and restore operations.

Managing a Database in SQL Server

You can perform various management tasks on a SQL Server database, such as:

– Alter: You can modify an existing database using the `ALTER DATABASE` statement.
– Drop: You can delete an existing database using the `DROP DATABASE` statement.
– Backup and Restore: It is essential to regularly backup your databases. SQL Server provides options for full backups, differential backups, and transaction log backups.

Example of altering a database to modify its size:

ALTER DATABASE MySampleDB
MODIFY FILE (NAME = MySampleDB, SIZE = 100MB);

Security in SQL Server Databases

Security is a critical aspect of database management. SQL Server provides several security mechanisms:

– Authentication: Determines who can log into the SQL Server instance.
– Authorization: Controls what resources a user can access within a database.
– Encryption: SQL Server supports encrypting data at rest as well as during transmission.
– Auditing: Allows you to track and log user activity and data changes.

System Databases in SQL Server

System databases are databases that are created when SQL Server is installed. These databases are used for various operational and management activities for SQL Server.

Types of System Databases

There are basically four system databases in SQL Server, master, msdb, tempdb and model that we can see. Apart from that, there is one more system database resource database that is hidden and read-only. Let’s move ahead with each system database.

Figure 1: System Database

Figure 2: System Database

Master Database in SQL Server

All the system-level information for a SQL Server record by the master database.
The dbid (database id ) of master is 1.
The master database has SIMPLE RECOVERY MODEL.
It is a very important database and we must have the backup.
Without the master database the server can’t be started.

Assume the master database files are missing or inaccessible, will SQL Server start or up?

Answer: No, SQL Server will not start because the master database is the important database and all the configuration and information needed to start the SQL Server is stored in the master database itself hence without the master database SQL server will not start.

Master database contains information about the server configuration. We can see the server configuration with the following query:
select * from sys.sysconfigures;
Master database contains information about all other databases and their location on SQL Server. We can see the information with executing the following query:
select * from sys.sysdatabases; or sp_helpdb
Master database contains information about logins in SQL Server. The following is the query by which we can see it:
select * from sys.syslogins;
Master database also contains information about users on SQL Server. The following is the query to see user details:
select * from sys.sysusers;
Master and mastlog are the logical file names of master database.
master.mdf ( data file ) and mastlog.ldf are the physical files of master database.

Query to see the physical file location of the master database:

SELECT name, physical_name FROM sys.database_files;

Figure 3: Master Database

For more information on the master database use the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-2017

Model Database in SQL Server

The Model database acts as a template database used in creation of new databases.
The dbid of the model database is 3.
By default the model database has FULL RECOVERY MODEL.
Figure 4: Recovery Model
We can take a backup of the model database.
Modeldev and modellog are the logical file names of the model database.
Model.mdf ( data file ) and modellog.ldf are the physical files of the model database.
The same query can be use to see the physical file location of the model database:

SELECT name, physical_name FROM sys.database_files;

Figure 5: Data Table

A user has created a new database. What will be the recovery model of that database?

Answer: Because the model database acts as a template database, when a user creates a new database it will inherit the property of the model database and as we know by default the recovery model of the model database is FULL (until or unless the user changes it), hence the new database is created by the user with FULL RECOVERY MODEL.

For more information on the model database use the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2017

MSDB Database in SQL Server

A MSDB database stores information related to backups, SQL Server Agent information, SQL Server Jobs, alerts and so on.
The Dbid of the msdb database is 4.
The recovery model of a msdb database is SIMPLE.
We can take backup of a msdb database.
MSDBData and MSDBLog are the logical file names of a msdb database.
MSDBData.mdf (data file) and MSDBLog.ldf are the physical files of a msdb database.
The same query can be used to see the physical file location of a msdb database:

SELECT name, physical_name FROM sys.database_files;

Figure 6: MSDB database Table

For more information on the msdb database use the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-2017

TempDB in SQL Server

It stores temporary objects, like temporary tables, temporary Stored Procedures and temporary tables to store sorting and so on.
The dbid of a temp database is 2.
The recovery model of a temp database is SIMPLE.
We can’t take a backup of a tempdb.
tempdev and templog are the logical file names of tempdb.
tempdb.mdf (data file) and templog.ldf are the physical files of a tempdb.
Same query can be use to see the physical file location of tempdb:

SELECT name, physical_name FROM sys.database_files;

Figure 7: TempDB Table

Why we can’t take a backup of a temp database?

Answer: Temp databases, as the name says, are used to do temporary operations, such as tables, Stored Procedures and cursors. Once the operation is over it will be cleared and is minimally logged. A TempDB is recreated everytime SQL is started, so it is always has a clean copy of the database hence backup and restore operations are not allowed for a TempDB.

How you will check to determine if the SQL Server is restarted?

Answer: Check the creation date of the tempdb, if it is new it means SQL Server is started.

Figure 8: Database Properties

We can execute the following query also to check the tempdb creation date:

select name, crdate from sys.sysdatabases;

Figure 9: Output

For more information on tempdb use the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-2017

Resource Database in SQL Server

It is a read-only database hidden from the user. It contains all the system objects that are included with the SQL Server.
The Dbid of the resource database is 32767.
The Resource database helps when we do a SQL Server upgrade.
We can’t see the resource database in SQL Server Management Studio but we can see its database file at the OS level by the name mssqlsystemresource.mdf and mssqlsystemresource.ldf in the Binn folder of Program Files.

Figure 10: Microsoft SQL System

We can see the location of the resource database file using the following query also:

Use master GO
SELECT
‘ResourceDB’ AS ‘Database Name’,
NAME AS [Database File],
FILENAME AS [Database File Location]
FROM
sys.sysaltfiles
WHERE
DBID = 32767 GO

For more information on the resource database use the following link:

https://blogs.msdn.microsoft.com/vsanil/2012/11/02/resource-database-common-questions/

What User Databases are: User databases are databases created by the user themself.

Figure 11: User Database

This is all about the SQL Server databases.

A data structure that stores data in memory in an organized form, along with facilitating functions such as data manipulation and effective management of data, having a particular structure that contains various tables. With each table being unique and storing data of a separate concept in the form of records represented by rows, and columns in the table representing fields, with the tables being linked to other tables through fields known as keys, is referred as the database in SQL.

Databases also provide indexes for easy access to data. Databases can be relational and non-relational depending on the type of information the user wants to store. Relational databases are those in which data is stored in the form of rows and columns in a table whereas non-relational databases do not use tabular format to store the data instead the data is stored either in graphical, document, columnar or key-value form. SQL is a relational database and hence stores the values in rows and columns format. Every column in SQL is called a field that is designed to store specific information about a record. Information/data of a single entity is stored in a record/ row having valid values of all columns. Columns have a vertical structure in SQL and rows have horizontal.

How to create a Database in SQL?

Before storing and accessing any information in a database, the first step is to create a database. The database in SQL is created by the CREATE DATABASE statement.

Syntax

CREATE DATABASE db_name;

In the above syntax, db_name is the name of the database that the user wants to give to the new database. It is necessary that the user must have admin privileges to create a new database.

Example

CREATE DATABASE employeeDB;

It will create a new database in SQL with the name employees. We can also check the above-created DB using the command given below:

SHOW DATABASES;

It will show the list of all the databases which are present in the SQL server till then.

How to Drop a Database in SQL?

Dropping a database means deleting an existing database from SQL Server. It is a good practice to delete the database that is not in use as it saves memory. DROP is a DDL command. In SQL DROP DATABASE statement is used to drop all the table that exists inside the particular database and delete that database.

Syntax

DROP DATABASE db_name;

In the above syntax db_name is the name of the database which we want to delete. One must be very careful while using the DROP command as the operations of the DROP command cannot be rolled back. Users must have admin privileges to drop the database.

Example

DROP DATABASE employeeDB;

If we try to delete a database that does not exist, SQL will show an error for the missing database which we are trying to delete. After dropping any database, we can check using the SHOW DATABASES; command and the deleted database will not be present in the list of available databases.

How to Fetch the Data from the Database?

After storing/ inserting the data into the database tables, one of the important tasks is to fetch the data and display the results to the user in a particular format. In SQL, the SELECT statement is used to retrieve the data from the database. The SELECT statement can be used in different forms according to the requirements of the user of what data it wants to fetch.

To fetch all the data of database * is used.
To fetch specific columns from the database, the column name is written after the SELECT keyword.
In order to retrieve data according to some conditions WHERE clause is used along with the SELECT statement.

Syntax 1: To fetch all the data from the table.

SELECT * FROM tb1;

In the above syntax, tb1 is the name of the table from which we want to fetch the data. (*) is used to retrieve the data of all the fields/columns present in the database.

Example 1

Consider a table name ‘Emp_details’ having the various columns and data mentioned below:

Emp_code	Emp_name	Emp_city	Emp_phno
101	Rahul	Noida	7894561236
102	Ankit	Delhi	9236547896
103	Sonam	Agra	9256347895

Example 2

SELECT * FROM Emp_details;

Output:

Number of Records: 3

Emp_code	Emp_name	Emp_city	Emp_phno
101	Rahul	Noida	7894561236
102	Ankit	Delhi	9236547896
103	Sonam	Agra	9256347895

Syntax 2: To fetch specific columns from the table.

SELECT column 1, column 2, column... from tb1;

Example

SELECT Emp_code, Emp_name, Emp_address from Emp_details;

Output:

Number of Records: 3

Emp_code	Emp_name	Emp_city
101	Rahul	Noida
102	Ankit	Delhi
103	Sonam	Agra

Syntax 3: To fetch data according to the given condition.

*SELECT from tb1 WHERE Emp_code =103;**
Output: Number of Records: 3
Emp_code	Emp_name	Emp_city	Emp_phno
103	Sonam	Agra	9256347895

How to Update the Database in SQL?

It is not necessary that the values once stored in the table remain the same in the future. For example, in an employee table, employee address, phno can be changed and needs to be altered in the database once reported. To update the values of the table in the database, the UPDATE statement is used in SQL. UPDATE modifies the values in the database of one or more records at a time according to the specific condition given by the user.

Syntax

UPDATE tb1 SET col 1= val 1, col 2= val 2 WHERE condition;

In the above syntax, ‘tb1’ is the name of the table on which data we want to make updation and ‘col 1’, ‘col 2’ are the name of columns of table whose values we want to modify and ‘condition’ specifies the condition of selection of rows on which basis updation needs to be done.

Example

UPDATE Emp_details SET Emp_phno = ‘8746456789’ WHERE Emp_code = 101;

Output:

Rows affected: 1

We can check the updation of Emp_phno of Employee having Emp_code 101 by fetching the table data:

SELECT Emp_code, Emp_phno from Emp_details WHERE Emp_code=101;
Output: Number of Records: 1
Emp_code	Emp_phno
101	8746456789

Conclusion

There are various operations that can be done in the SQL Database and the queries can be written in many forms in order to access the data according to the requirement. It is very important and considers a good practice to write optimized queries while doing any operation and retrieve only those records which are required at that particular time as this increases the execution speed and saves memory.

SQL Server DBA Training from Beginner to Advanced

Databases in SQL Server

Introduction

Types of Databases in SQL Server

System Databases

User Databases

Creating a Database in SQL Server

Database Files and Filegroups in SQL Server

Filegroups

Managing a Database in SQL Server

Security in SQL Server Databases

System Databases in SQL Server

Types of System Databases

Master Database in SQL Server

Assume the master database files are missing or inaccessible, will SQL Server start or up?

Model Database in SQL Server

A user has created a new database. What will be the recovery model of that database?

MSDB Database in SQL Server

TempDB in SQL Server

Why we can’t take a backup of a temp database?

How you will check to determine if the SQL Server is restarted?

Resource Database in SQL Server

How to create a Database in SQL?

How to Drop a Database in SQL?

How to Fetch the Data from the Database?

Syntax 1: To fetch all the data from the table.

Syntax 2: To fetch specific columns from the table.

Syntax 3: To fetch data according to the given condition.

How to Update the Database in SQL?

Conclusion

Login with your site account

Register a new account

Modal title