SQL Server CREATE DATABASE

A database is an organized collection of data that is structured into tables, rows, columns, and indexes. It helps the user to find the relevant information frequently. It enables the user to access and manage the records through the database very easily. Usually, each database in SQL Server stores all files in the form of tables.

Why we need a database?

Most companies and organizations need a database because it maintains all of its relevant information, such as employee records, transactional records, salary information, etc. The following are the popular reasons for the need for the database:

Manages large amounts of data: Each database can store and manage a large volume of data at a single place on a daily basis. There are no other tools available, including a spreadsheet, to do the same.

Accurate: Each database can store the data accurately because it provides built-in constraints, checks, and other features. Therefore, database information is almost always guaranteed to be correct.

Easy to update: A user can easily update the database using various Data Manipulation languages (DML) commands such as SQL, SQL Server, etc.

Security: Each database enables users to ensure the security of data using various methods. For example, a login is required to access the database or give the rights to access only specific parts of the database, not all.

Data integrity: Each database ensures that the stored data is accurate and consistent. A user can do this by using various constraints for data or conform to ACID property rules.

Easy to research data: A database enables users to search and find the relevant information quickly using the Data Query Languages (DQL).

SQL Server provides two types of database:

1. System databases
2. User Databases

System Databases: The system databases are created automatically while installing the MS SQL Server on our system. It plays an essential role in the server such database run perfectly. The following are the list of system databases in SQL Server:

• Master
• Model
• MSDB
• Tempdb

User Databases: The user databases are created by database users like DBAs and testers who can access a database also.

We can create a database in SQL Server mainly in two ways:

• Transact-SQL Command
• SQL Server Management Studio

Let us discuss them one by one in detail.

CREATE DATABASE using T-SQL Command

The below syntax enables the users to create a new database in SQL Server:

In the above syntax, the database_name indicates the name of a new database.

Rules to create a new database

We must follow the following rules for creating a new database:

• The name of a new database should be unique within a particular server instance.
• Each database should have a maximum of 128 characters.
• The CREATE DATABASE command should be executed in auto-commit mode.

Example

Let us understand how to create a database in SQL Server through an example. To do this, first, open the Management Studio and connect with the database engine by giving the server address and the authentication details. Then, click a new query from the standard bar.

Once the connection becomes successful, execute the below statement to create a new database named DemoDb:

We can see the newly created database in the Object Explorer once the statement has been successfully executed. If it does not show here, we need to click the refresh button or pressing the F5 on our keyboard to update the object list. See the below image:

SQL Server allows the user to list all databases stored in the database engine by using the following command:

Executing the statement will display the list of all databases:

SQL Server allows the stored procedure statement to list all databases stored in the database engine as below:

Executing the stored procedure will display the list of all databases, including its size and remarks if any. Otherwise, this field is shown NULL.

CREATE DATABASE using SQL Server Management Studio

SQL Server Management Studio is a GUI tool that enables DBA to configure, manage, and administer all components of SQL Server instead of using the command line. We can create a database in SSMS with the help of the following steps:

Step 1: Open the SSMS in administrator mode to avoid any permission issue. We will see the below screen where we will first connect with the server. Here, we must fill in the server name, server type, authentication information and then click on Connect button to continue.

Step 2: Once the connection becomes successful, the Object Explorer window will appear on the left-hand side of the screen. The server we are connected to is shown at the top of the Object Explorer. To see the Database folder, click the "+" button if it isn't extended.

Step 3: The next step is to right-click on the Databases folder and choose a New Database from the dropdown list to create a database.

Step 4: The next step will open the New Database dialog box. Here we can configure the database before creating it. Now, type the database name, change the setting if required, and then click the Ok button. In most cases, the DBA leaves the settings at their default.

Step 5: Once the database creation is successful, we can see them by expanding the Databases folder under the Object Explorer. The database icon has a cylinder icon.

This article will explain the complete overview of creating a new database in SQL Server using the SQL Server Management Studio (SSMS) and SQL command.