How to Create a Database in SQL Server 2019 [Step by Step]

In this SQL Server 2019 tutorial, we will discuss what is SQL Server Database, Types of Databases in SQL Server, and How to Create a Database in SQL Server 2019, and will cover the below topic.

  • Introduction to SQL Server Database
  • Types of SQL Server Databases
  • Login vs User in SQL Server
  • How to create a Database in SQL Server 2019
  • How to create Database in SQL Server Management Studio

Introduction to SQL Server Database

create a Database in SQL Server 2019
SQL Server Database Structure
  • A SQL Server Database can be defined as an organized collection of data that is stored electronically in a computer system.
  • Each Database in SQL Server can store a set of database objects such as tables, views, stored procedures, etc., and the database stores this data in a structured manner.
  • SQL Server Databases are saved as files on the file system and each database has two operating system files:
create a Database in SQL Server
Database Files
  1. The Data files: It contains the actual database objects such as tables, views, etc.
  2. The Log files: It contains all the information which is required for the recovery of transactions in a database.

Read: How to install sql server 2019 express on windows 10 step by step

Types of SQL Server Databases

The SQL Server mainly consists of two types of databases:

  1. System Databases
  2. User Databases

System Databases

system databases types in sql server 2019
Types Of System Databases

The System Databases consists of mainly 5 database files which are created automatically at the time when we install SQL Server and each database has its own significance which is mentioned below:

System database
Description
Master DatabaseThe Master Database contains all the information related to SQL Server Configuration and all the metadata information related to database objects (tables, views, procedures, etc.)
Model DatabaseThe Model Database comes to use whenever a new database is created on the SQL Server. It is used as a template that provides a basic structure to every newly created database on SQL Server instance.
If we modify anything in the Model Database such as database size, database option, etc. it will be applied to all the databases created afterward.
Msdb DatabaseThis database is primarily used by SQL Server Agent to store system-related activities like SQL Server jobs, maintenance plans, backup history, etc. This database plays an important role in the proper functioning of SQL Server Agent Service.
Tempdb DatabaseThe tempdb is a temporary location for storing temporary objects or intermediate result sets. This database is created using the model database by SQL Server instance with SQL Server Service starts.
Resource DatabaseThe Resource Database is a read-only database that holds SQL Server system objects. The System objects are physically stored in the Resource database, while they are logically kept in every database’s sys schema.

User Databases

Users construct their own user databases (Administrators, developers, and testers who have access to create databases). These databases are completely managed by the end-user itself.

We can create our own databases either by using command-line tools such as sqlcmd or we can use SQL Server Management Studio to create a database using a user interface.

Read: How to export data from SQL Server to Excel

Login vs User in SQL Server

So let’s discuss an important security concept related to the databases in SQL Server ie. Login and User. People often consider these concepts to be the same but it is not true.

Login in SQL Server

A login is a server-level security principal or an entity that is used to authenticate users by a  secure system. It is just a basic SQL Server login credential that is used to access the SQL Server.

For example, we provide our username and password when logging on to Facebook or any other social media website. Users may need a login to connect to SQL Server instance. Now in SQL Server there are mainly two types of login’s, Windows authenticated login and SQL Server authenticated login.

SQL Server also allows us to create logins using certificates or asymmetric keys which are exclusively used for code signing. We can’t connect to SQL Server using them.

Windows Authenticated Login

This login is based upon windows credentials which allow users to log in to SQL Server using windows username and password.

SQL Server Authenticated Login

The SQL Server authenticated logins are those which are not based upon Windows user accounts and are created and stored in SQL Server. When utilizing SQL Server Authentication, users must enter their credentials each time they log in.

User in SQL Server

A user in an SQL Server is a database-level security principal which is used to gain access to a database instance. A login can be mapped to multiple databases as distinct users, but each database can only have one user. We can assign Permissions to users as a security principle.

The scope of a user is restricted to a database. Login must be mapped to a database user to connect to a specific database on a SQL Server instance.

Database permissions are granted and denied to the database user, not the login. There are a total of 12 types of users that we can create using SQL Server which is listed below.

Users that cannot authenticate 

These users are cannot access SQL Server or the SQL Database.

  • User without a login– They cannot log in to SQL Server Database, but they can be granted some permissions.
  • User based on a certificate / User based on an asymmetric key– These user types also cannot access the Database, but they can be granted permissions and can sign modules.

Users based on logins in master

  • In this, a user based on a Windows Active Directory account is used to be authenticated.
  • A user with a login that is based on a Windows group.
  • A user is created based on a SQL Server login.

Users that authenticate at the database

  • A user based on a Windows account who does not have a login.
  • A user with no login based on a Windows group.
  • A user based on an Azure Active Directory user.
  • Password-protected database user.

Users who connect through Windows group logins and are based on Windows principles

  • A user based on a Windows account who does not have a login but can access the Database Engine via a Windows group membership.
  • A user who is a member of a Windows group but does not have a login but can connect to the Database Engine via another Windows group.

Read: SQL Server 2019 New Features

How to create a Database in SQL Server 2019

In SQL Server 2019 we can maximum create 32,767 databases in a particular SQL Server instance and it is also recommended to backup the master database file whenever a new database is created, modified, or deleted.

There are mainly 2 ways through which we can create a Database in SQL Server 2019. The first is by using any command-line utility and the second one is by using SQL Server Management Studio

Read: Advanced Stored Procedure Examples in SQL Server

How to create a Database in SQL Server 2019 using sqlcmd

For SQL Server 2019 there are many command-line utilities available one of them is sqlcmd, which comes by default when we install SQL Server 2019. Now sqlcmd is a command-line tool for running SQL Server queries, T-SQL statements, and SQL Server scripts.

It allows users to connect to SQL Server instances, send Transact-SQL batches from them, and output rowset information from them.

Now lets see the steps for creating a Database in SQL Server 2019 using sqlcmd

Step 1. Run the Windows Command Prompt and execute the following command to connect to a SQL Server Database instance.

sqlcmd -S <Server_Name>

Here -S option is used specify the SQL Server instance name

create a database in sql server 2019 using sqlcmd
Connect to Database Using SQLCMD

The number 1> means that it is connected and ready to receive sentences to execute

Step 2. Now we can list all the available Databases in SQL Server instance using the following command

SELECT NAME FROM master.sys.databases

master.sys.databases is the location where all the database related information is stored.

How to create Database in SQL Server 2019
Query To List Databases

It will return the following output

How to create a database in sql server 2019 using sqlcmd
List Of Databases

Step 3. Now we will use the CREATE DATABASE command, which is a Data Definition Language(DDL) SQL command used to create Databases.

CREATE DATABASE <DATABASE_NAME>
creating a Database in SQL Server 2019 using sqlcmd
creating a Database in SQL Server 2019 using sqlcmd

Step 4. Now after the execution, we can use the command given in Step 2 to again list all the databases and it is clearly visible that the newly created database is added into that list.

create Database in SQL Server 2019 Using Command-Line Utility
New Database List

This is how to create a Database in SQL Server 2019 using sqlcmd.

Read: How to create a table in sql server management studio

How to create Database in SQL Server Management Studio

SQL Server Management Studio (SSMS) is a blended environment provided by Microsoft to manage SQL infrastructure. This application combines a variety of graphical interfaces with a number of powerful script editors.

There are two ways to create a database using SQL Server Management Studio (SSMS)

  1. Using Graphical User Interface.
  2. Using Transact-SQL Quieres.

Both the methods are explained bellow

Using Graphical User Interface

Step 1. The first step is to start the SQL Server Management Studio(SSMS) and connect with the Database Engine and for this enter the database access credentials and click on “Connect” to connect to the database instance.

create Database in SQL Server Management Studio
Connect to Database Engine

Step 2. Now go to Object Explorer and expand the server node, right-click on the “Databases” option and click on “New Database“. It will open a New Database Window.

How to create Database in SQL Server Management Studio
New Database Option

Step 3. In the New Database Window, enter the database name and click on “OK” to create a new database.

how to create database in sql server 2019 using management studio
New Database Window

Step 4. Now the newly created database will be visible in the Object Explorer, under the Databases directory.

create database in sql server 2019 using management studio
Newly Created Database

Using Transact-SQL Query

Step-1. First step is to connect with the Database Engine using database access credentials.

Step-2. Now in SQL Server Management Studio, go to the Menu bar options and click on “New Query” or click “CTRL + N“. It will open an empty SQL file in the Management Studio.

Create Database in SQL Server Management Studio
New Query Option

Step-3. Now enter the following query in the empty SQL file.

CREATE DATABASE <DATABASE_NAME>
create a database in sql server 2019 using sqlcmd
Create Database Quey

Step-4. After writing the query, go to the Menu bar and click on the “Execute” option to execute the written query.

How to create a database in sql server 2019 using sqlcmd
Execute Option

Step-5. Once the query has been successfully executed we will get the following message

creating a Database in SQL Server 2019 using sqlcmd
Succesfull Query Execution Message

You may like the following sql server tutorials:

In this SQL Server 2019 tutorial, we have learned what is SQL Server Database, Types of Databases in SQL Server, and How to Create a Database in SQL Server 2019, and will cover the below topic:

  • Introduction to SQL Server Database
  • Types of SQL Server Databases
  • Login vs User in SQL Server
  • How to create a Database in SQL Server 2019
  • How to create Database in SQL Server Management Studio
  • Create a Database in SQL Server 2019 using sqlcmd

Leave a Comment