CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database. A database is a structured collection of data, organized for efficient storage, retrieval, and management.

Syntax

The basic syntax for creating a database can vary slightly between different SQL database systems (e.g., SQL Server, MySQL, PostgreSQL), but the core concept remains the same.

SQL Server Syntax


CREATE DATABASE database_name
[ ON
    [ PRIMARY
        ( NAME = logical_file_name,
          FILENAME = 'os_file_name',
          SIZE = size,
          MAXSIZE = { max_size | UNLIMITED },
          FILEGROWTH = growth_increment )
    ]
    [ , SECONDARY
        ( NAME = logical_file_name,
          FILENAME = 'os_file_name',
          SIZE = size,
          MAXSIZE = { max_size | UNLIMITED },
          FILEGROWTH = growth_increment )
    ]
]
[ LOG ON
    {  [ ,...n ] }
]
[ COLLATE collation_name ]
[ WITH

MySQL Syntax


CREATE DATABASE [IF NOT EXISTS] database_name
    [CHARACTER SET charset_name]
    [COLLATE collation_name];

PostgreSQL Syntax


CREATE DATABASE database_name
    [ WITH ]
    [ OWNER [=] user_name ]
    [ TEMPLATE [=] template ]
    [ ENCODING [=] encoding ]
    [ LOCALE [=] locale ]
    [ LC_COLLATE [=] lc_collate ]
    [ LC_CTYPE [=] lc_ctype ]
    [ TABLESPACE [=] tablespace_name ]
    [ ALLOW_CONNECTIONS [=] allowconn ]
    [ CONNECTION LIMIT [=] connlimit ]
    [ IS_TEMPLATE [=] istemplate ]
    [ OMITTED ];

Parameters Explained

Common Parameters:

Parameter	Description
`database_name`	The unique name for the new database.
`logical_file_name`	The logical name of the file in the database.
`os_file_name`	The operating system file name (e.g., 'C:\path\to\data.mdf').
`SIZE`	The initial size of the file. Specified in megabytes (MB) or gigabytes (GB).
`MAXSIZE`	The maximum size the file can grow to. Can be a specific size or `UNLIMITED`.
`FILEGROWTH`	The increment by which the file grows when it needs more space. Can be a fixed size (MB/GB) or a percentage.
`CHARACTER SET` (MySQL)	The character set to use for the database (e.g., `utf8mb4`).
`COLLATE`	The collation for the database, which determines character sorting and comparison rules.

Examples

Example 1: Creating a simple database (SQL Server)

This example creates a database named MyNewDatabase with default settings.


CREATE DATABASE MyNewDatabase;

Example 2: Creating a database with specific file settings (SQL Server)

This example creates a database with a primary data file and a log file, specifying initial sizes and growth increments.


CREATE DATABASE SalesDB
ON
PRIMARY
(
    NAME = SalesDB_Data,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SalesDB.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 10%
)
LOG ON
(
    NAME = SalesDB_Log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SalesDB_log.ldf',
    SIZE = 50MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
);

Example 3: Creating a database (MySQL)

This example creates a database named customer_data using UTF8 character set.


CREATE DATABASE IF NOT EXISTS customer_data
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Best Practices

Use descriptive and meaningful names for your databases.
Carefully consider the initial size and growth increments for data and log files to balance performance and disk space usage.
Specify a suitable character set and collation based on the data you intend to store.
For production environments, it's often recommended to place data files and log files on separate physical drives for better performance and recoverability.
Regularly monitor database file sizes and growth to prevent unexpected disk space exhaustion.

SQL Documentation