A database is a collection of small units of information that is used to organize data in pre-defined relationships in the order of tables containing columns and rows. You can think of a database as the highest-level container with other objects holding the data. A database is responsible for keeping things such as tables, triggers, functions, custom data types, tables, views, and more. Therefore, when you need to store specific data, you will need to ensure you have a database in which you can define the structure of your data.

This article aims to help you understand how to create and drop databases in SQL Server using various methods.

SQL Server Create Database (Transact-SQL)

The first and most common database creation method in SQL Server is the CREATE DATABASE statement.

The statement follows a syntax as shown:

We start with the CREATE DATABASE keyword, followed by the name of the database you wish to create. It is good to ensure that the database name adheres to SQL Server identifier naming rules.

SQL Server also limits the database name to 128 characters.

For example, to create a database called linuxhint, we can run the command:

Once we execute the command above, we can access the created database using the DB explorer or using the terminal.

For example, the following shows the created database in a Graphical Interface.

To show all databases in the server using the Transact-SQL statement, run the following:

This should list the names of all the databases in the server as follows:

Note that any SQL Server recommends backing up the master database before creating, modifying, or dropping any user database in the server.

Depending on the server configuration, the command may require the CREATE DATABASE permission on the master database.

SQL Server Drop Database – Transact SQL.

We can use a Transact-SQL command to drop an existing database as shown in the syntax below:

— SQL Server Syntax

The IF EXISTS clause allows you to conditionally drop a database if it exists. If the database with the specified name does not exist, the server does not return an error.

For example, to drop the linuxhint database we created previously, run the command:

This should remove the database with the specified name from the server.

SQL Server Drop Database – Using SQL Server Management Studio

We can also use a graphical method to drop an existing database from the server.

In SSMS, open the object explorer and expand the databases option. Locate the database you wish to remove, right click, and select delete.

This should open a new dialog allowing you to select the options to include when deleting the database. To avoid any connection collision, select “Close existing connections.”

Conclusion

In this post, we explored the basics of creating and deleting a database using Transact-SQL commands and Graphical Interfaces.