Create Database in SQL Server 2019

In SQL Server, a database is made up of a collection of objects like tables, functions, stored procedures, views etc. Each instance of SQL Server can have one or more databases. SQL Server databases are stored in the file system as files. A login is used to gain access to a SQL Server instance and a database user is used to access a database. SQL Server Management Studio is widely used to work with a SQL Server database.

Type of Database in SQL Server

There are two types of databases in SQL Server: System Database and User Database.

System databases are created automatically when SQL Server is installed. They are used by SSMS and other SQL Server APIs and tools, so it is not recommended to modify the system databases manually. The followings are the system databases:

master: master database stores all system level information for an instance of SQL Server. It includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
model: model database is used as a template for all databases created on the instance of SQL Server
msdb: msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
tempdb: tempdb database is used to hold temporary objects, intermediate result sets, and internal objects that the database engine creates.

User-defined Databases are created by the database user using T-SQL or SSMS for your application data. A maximum of 32767 databases can be created in an SQL Server instance.

There are two ways to create a new user database in SQL Server:

Create Database Using T-SQL
Create Database using SQL Server Management Studio

Create Database using T-SQL Script

You can execute the SQL script in the query editor using Master database.

Syntax:
USE master; CREATE <database-name>

The following creates 'HR' database.

USE master;
CREATE DATABASE 'HR';

The Following create 'HR' database with data and log files.

USE master;
CREATE DATABASE [HR] 
    ON (NAME = N'HR', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL15.SQLEXPRESSMSSQLDATAHR.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'HR_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL15.SQLEXPRESSMSSQLDATAHR_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)

Make sure that the data and log file path exist before executing the above SQL script.

Now, open SSMS and refresh the databases folder and you will see 'HR' database is listed.