How to Create Database If Not Exists in SQL Server?

by

In this SQL Server tutorial, you will understand how to create database if not exists in SQL Server.

You will learn about manual methods that you can use to check the existence of any database before creating a new one.

Create Database If Not Exists in SQL Server

To create Database if not exists in SQL Server, you will use conditional, system views, and SQL statements together.

But first, let’s know some reasons for checking the existence of a database before creating a new database.

  • When you create a database that already exists on your SQL Server, then you get the error. So, for error prevention, you can check the existence of the database before creating it.
  • You can also protect your data; the process of database creation involves initializing its structure, and can delete the data if the same name of the database already exists and contains information.

SQL Server doesn’t have any clause like CREATE DATABASE IF NOT EXISTS, you need to manually check the existence of any database using the conditional logic with CREATE DATABASES statement.

You can use the below syntax.

IF NOT EXISTS(SELECT name FROM sys.databases WHERE name = 'database_name')
BEGIN
        CREATE DATABASE new_database_name;
END

Now open the SQL Server Management Studio and connect to the SQL Server instance, then press CTRL + N to open the New query window where you can write the SQL query.

In that query area, write the below query to create the database named Orders.

IF NOT EXISTS(SELECT name FROM sys.databases WHERE name = 'Product_Orders')
BEGIN
        CREATE DATABASE Product_Orders;
END
create database if not exists in sql server

When you execute the above, it first checks for the existence of the Product_Orders database using the conditions IF NOT EXISTS(SELECT name FROM sys.databases WHERE name = ‘Product_Orders’).

But there is no existence of any database named Product_Orders on SQL Server, so the next query gets executed and creates a new database named Product_Orders.

Now again, run the same query and see ‘What happens?”. When you run the above query again it runs without showing an error because this time it finds the existence of the Product_Orders database.

So then it ignores the error and doesn’t create a new database. Here, you need to understand how to check the existence of a database before creating it so that it does not show any error or doesn’t delete the data of the already existing database.

Conclusion

In this SQL Server tutorial, you learned how to create a database if it does not exist in SQL Server. Also, you learned the reason behind checking the existence of the database you will create.

You may also like the following tutorials: