Creating and Working with Database

Hi friends! In our last post we have seen different approaches how we can connect with SQL Server Management Studio(SSMS).

Today, We are going to learn how to create a database and perform tasks on database such as Creating, Altering and Dropping our Database.

So, Let's start with Creating a Database.
We can Create, Alter or Drop a database with 2 methods. Which are

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

Let's describe each method one-by-one.
Firstly, to create a database graphically we have to follow some steps

1. Expand the Database Server by clicking on "+" icon as shown in the picture.

   

   Database Server

2. Right Click on Databases folder in the Object Explorer
   
3. Click on the New Database option
4. You will be prompted to enter name of New Database and user to make it as the owner.
5. In the New Database dialog box, enter the Database name (MyFirstDatabase).

   

   Adding name to database

6. If You want to change the owner of Database click on the button with ... icon, for default settings we keep it untouched.
7. Click OK.
8. Now if you Refresh the databases folder, you can see your Database is created.

   

   New database created

Now, Let's discuss the second method to create a database using a query.
Query to Create Database is:

Create database DatabaseName

Now, Let's find out the background process, what actually is getting executed behind the scenes.
So, Whenever you create a database either graphically or with the help of a Query, the following 2 files gets generated.

• .MDF file - Data File (Contains actual data) //usually large in size
• .LDF file - Transaction Log file (Used to recover the database)  // small in size

Now, lets start with the operation on database. To Alter database which we have already created

Alter database DatabaseName Modify Name = DatabaseName

Alternate way to achieve this task is, you can also use system stored procedure 'sp_renameDB'

Execute sp_renameDB 'OldDatabaseName','DatabaseName'

To Delete or Drop an existing database, we can use Drop Statement

Drop Database DatabaseThatYouWantToDrop

Dropping a database, deletes the LDF and MDF files.

If any database is already in use then we cannot perform drop or delete command over it.
You get an error stating - Cannot drop database "DatabaseThatYouWantToDrop" because it is currently in use.
If you still want to delete that type of databases from your server then we have an approach in SQL Server to put that Database in Single User Mode and then we can perform the drop command execution.

To bring database in Single User Mode

Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate

Rollback Immediate option will rollback all the pending Transaction and closes all the connections to the database.

System databases cannot be dropped.

Summary:
So, Guys this is all about Creating and Working with Database

I Hope in this post covered all the points about how we can make a database and perform Alter, Drop and Delete commands on Databases, which will be helpful to understand the concept how SQL Server works.

Please share this post with your friends and colleagues.
For any queries please post a comment below.

Happy Coding 😉