Creating a Database
In SQL Server, the CREATE DATABASE statement is used to create a new database. A database provides a container where tables and other database objects can be stored.
What is CREATE DATABASE?
The CREATE DATABASE statement creates a new database in SQL Server.
Once a database is created, we can create tables and other database objects inside it.
CREATE DATABASE CollegeDB;
Database Creation Flow
The basic process of creating a database can be understood as follows:
SQL Server
↓
CREATE DATABASE
↓
CollegeDB
↓
Create Tables
↓
Insert Data
Create a Simple Database
The following statement creates a database named StudentDB.
CREATE DATABASE StudentDB;
After executing the statement, SQL Server creates the StudentDB database.
Verify the Database
We can check whether the database exists by using the following query:
SELECT name
FROM sys.databases;
This query displays the databases available on the SQL Server instance.
Output
After executing the query, the database name can be seen in the result.
| Database Name |
|---|
| master |
| model |
| msdb |
| tempdb |
| StudentDB |
The exact list of databases depends on the SQL Server installation and databases available on the server.
Select a Database Using USE
After creating a database, the USE statement can be used to select that database for subsequent SQL operations.
USE StudentDB;
After executing this statement, subsequent table creation statements will be executed in the selected database.
Create Database and Table
We can create a database first and then create a table inside that database.
CREATE DATABASE StudentDB;
USE StudentDB;
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
Age INT,
Course VARCHAR(50)
);
What Can a Database Contain?
A SQL Server database can contain different types of database objects.
Tables
Store data in rows and columns.
Views
Provide a way to query data through a saved query definition.
Stored Procedures
Store reusable SQL statements and logic.
Functions
Perform reusable operations and return values.
Indexes
Help SQL Server locate data efficiently.
Constraints
Help enforce rules on table data.
Check Before Creating a Database
Before creating a database, we can check whether a database with the same name already exists.
IF DB_ID('TrainingDB') IS NULL
BEGIN
CREATE DATABASE TrainingDB;
END;
This prevents the CREATE DATABASE statement from being executed when the specified database already exists.
Database Naming Guidelines
- Choose a meaningful database name.
- Keep naming consistent across the project.
- Avoid unnecessarily complicated names.
- Use names that clearly describe the application's data.
Important
A database should be created with a meaningful name and should be organized properly before creating the application's tables and other database objects.
Summary
In this section, we learned how to create a database using CREATE DATABASE, verify databases using sys.databases, select a database using USE and create tables inside the selected database.