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
- 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:
- The Data files: These contain the actual database objects such as tables, views, etc.
- The Log files: These contain all the information which is required for the recovery of transactions in a database.
Types of SQL Server Databases
The SQL Server mainly consists of two types of databases:
- System Databases
- User Databases
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 Database | The 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 Database | The 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 Database | This 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 Database | The 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 Database | The 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 can use SQL Server Management Studio to create a database using a user interface.
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 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 who 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.
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
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.databasesmaster.sys.databases is the location where all the database related information is stored.
It will return the following output
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>
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.
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)
- Using Graphical User Interface.
- Using Transact-SQL Quieres.
Both 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.
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.
Step 3. In the New Database Window, enter the database name and click on “OK” to create a new database.
Step 4. Now the newly created database will be visible in the Object Explorer, under the Databases directory.
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.
Step-3. Now enter the following query in the empty SQL file.
CREATE DATABASE <DATABASE_NAME>
Step-4. After writing the query, go to the Menu bar and click on the “Execute” option to execute the written query.
Step-5. Once the query has been successfully executed we will get the following message
You may like the following sql server tutorials:
- Azure SQL database query history
- Azure SQL Database Schema
- Drop Database PSQL
- Connect to Azure SQL database using Python
- Azure SQL database column encryption
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.