SQL Create Database

The CREATE DATABASE statement is used to create a new, empty relational database within a database management system (RDBMS). Once created, this database serves as the root container for schemas, tables, views, stored procedures, and other database objects.

Basic Syntax

The fundamental syntax for creating a database is straightforward across almost all SQL database engines:

CREATE DATABASE database_name;

• database_name: Must be a unique name on the database server instance and conform to standard SQL identifier rules (usually alphanumeric, starting with a letter, and containing no spaces or special characters unless quoted).

Example

To create a new database named SalesData:

CREATE DATABASE SalesData;

Engine-Specific Syntax & Best Practices

In production environments, simply creating a database with default settings may not suffice. You often need to specify character sets, collations, file locations, or initial size limits.

1. MySQL / MariaDB (Specifying Character Set & Collation)

By default, databases should be created using utf8mb4 to fully support multi-language text, special symbols, and modern emoji characters.

CREATE DATABASE IF NOT EXISTS SalesData
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

• IF NOT EXISTS: Prevents the query from throwing an error if a database named SalesData already exists on the server.

2. PostgreSQL (Specifying Owner & Encoding)

In PostgreSQL, you can specify the database owner, encoding, and template database:

CREATE DATABASE sales_db
    WITH 
    OWNER = db_admin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    CONNECTION LIMIT = -1;

3. Microsoft SQL Server (Specifying File Locations & Sizing)

SQL Server allows DBAs to define the physical location, initial file sizes, and growth parameters for primary data (.mdf) and log (.ldf) files:

CREATE DATABASE SalesData
ON PRIMARY
(
    NAME = SalesData_Data,
    FILENAME = 'C:\SQLData\SalesData.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = SalesData_Log,
    FILENAME = 'C:\SQLLogs\SalesData_log.ldf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);

Selecting & Verifying Your New Database

After creating a database, you must switch your current active session to work inside that database.

Selecting a Database (USE)

Supported in MySQL, SQL Server, and MariaDB:

USE SalesData;

Note for PostgreSQL users: PostgreSQL does not support the USE statement. In psql (the command-line tool), switch databases using \c database_name.

Listing Existing Databases

To verify that your database was created successfully:

-- SQL Server
SELECT name FROM sys.databases;

-- MySQL / MariaDB
SHOW DATABASES;

-- PostgreSQL (Command line)
\l
-- Or via ANSI query:
SELECT datname FROM pg_database;

Important Rules & Considerations

1. Permissions: Executing the CREATE DATABASE statement requires administrative permissions (e.g., CREATE ANY DATABASE in SQL Server, CREATEDB role in PostgreSQL, or global CREATE privilege in MySQL).
2. Naming Conventions: Use lowercase letters or standard snake_case (sales_data_db) to prevent issues across operating systems with case-sensitive file systems (like Linux).
3. Check Existence First: Always use conditional checks (IF NOT EXISTS) in deployment or migration scripts to make them safe to re-run (idempotent).