Creating Altering and Deleting Database in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Creating Altering and Deleting Database in SQL Server

In this article, I am going to discuss Creating Altering and Deleting Database in SQL Server with examples. Please read our previous article where we discussed how to connect to SQL Server Database using a client tool called SQL Server Management Studio. As part of this article, we are going to discuss the following pointers.

1. Understanding the Different Types of Database in SQL Server.
2. Understanding the different mechanisms to create, alter and drop databases in SQL Server.
3. How to create a database graphically?
4. How to create a database using the query?
5. Understanding the Query to Alter and Drop database in SQL Server.

Different Types of Database in SQL Server.

In SQL Server we are going to interact with 2 types of databases such as

1. System databases
2. User databases

System Databases in SQL Server:

The databases which are created and managed by the SQL Server itself called System databases. SQL Server has four system databases as shown in the below image. 

Let us discuss the role and responsibilities of each of the above databases.

Master database: This database is used to store all system-level information such as system id, culture, server id no, server version, server culture, etc
Model database: The model database will act as a template for creating new databases under a server environment.
Msdb (Microsoft database): Microsoft database will store jobs and alerts information i.e. backup file information.
Tempdb database: It is a temporary database location that is allocated by the server when the user connected to the SQL Server for storing temporary table information.

Note: Once you disconnected from the SQL Server, then the temporary database location will be destroyed automatically. The above system databases are maintained and managed by the system by default.

User Databases in SQL Server:

The databases which are created and managed by the user are called User Databases. These databases are used for storing business-related information such as employee details, Customer Details, Student details, Product Details, Salary details, etc. In SQL Server, the user databases can be created, altered and dropped in two ways

1. Graphically using SQL Server Management Studio (SSMS) or
2. Using a Query

Creating SQL Server Database Graphically:

1. Right Click on the Databases folder in the Object Explorer
2. Select New Database
3. In the New Database dialog box, enter the Database name and click the OK button as shown in the below image.

Once you click on the OK button it will create the database as shown in the below image.

How to create SQL Server Database using Query?

The syntax for creating a database in SQL Server: Create database <Database Name>
Example: Create database TestDB

Select the above query and Click on either Execute option or F5 key for execution. Whether we create a database graphically using the designer window or using a query, the following 2 files get generated.

.MDF file: Master Data File (Contains actual data). This file will store all Tables data and will be saved with an extension of .mdf (master data file)

.LDF file: Transaction Log file (Used to recover the database). This file will store transaction Query information (insert, update, delete. Create, etc) and saved with an extension of .ldf (log data file)

Note: The above two files are used for transferring the required database from one system to another system or from one location to another location. The Root Location of .mdf and .ldf files: C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

How to Rename a database in SQL Server?

Once you create a database, then you can modify the name of the database using the Alter command as shown below.
Alter database DatabaseName Modify Name = NewDatabaseName

Alternatively, you can also use the following system-defined stored procedure to change the name.
Execute sp_renameDB ‘OldDatabaseName’,’NewDatabaseName’

How to Delete or Drop a database in SQL Server?

In order to delete or drop a database in SQL Server, you need to use the following DROP command.

Drop Database DatabaseThatYouWantToDrop

Whenever you drop a database in SQL Server, internally it deletes the LDF and MDF files. You cannot drop a database if it is currently in use and at that time you will get an error stating – Cannot drop database “DatabaseName” because it is currently in use. So, if other users are connected to your database, then first you need to put the database in single-user mode and then drop the database. In order to put the database in single-user mode, you need to use the following command.

Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate

With Rollback Immediate option, it will rollback all incomplete transactions and closes the connection to the database.

Note: System databases cannot be dropped.

In the next article, I am going to discuss Creating and working with database tables in SQL Server with examples. Here, in this article, I try to explain Creating Altering and Deleting Database in SQL Server step by step with examples. I hope you enjoy this article and got an overview of how to create and manage databases in SQL Server.