SQL-Database

In this tutorial, you will learn how to about database, create a new database, modify database and drop database in SQL Server. The following things related to database will learn:

1. Database in SQL Server
2. Features or Benifits of a database
3. Type of Database in SQL Server
4. Database Creating Rules
5. Create Database in SQL Server
6. Create Database with File Location
7. View Created Database
8. Modify Database
9. Drop Database
10. Drop with IF EXISTS Database

1. Database in SQL Server

A database is an organized collection of objects like tables, rows, columns, and indexes, functions, stored procedures, views etc. Each instance of SQL Server can have one or more databases. . Each database in SQL Server stores all files in the form of tables. A login iss 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.

2. Features or Benifits of a database

There are some important features of all databases that make them incredibly valuable for managing data.

1. Databases can scale as your business grows
2. Databases can handle multiple users with ease
3. Databases store information accurately and reliably
4. Databases allow you to avoid redundant information
5. Databases can process your data in powerful and interesting ways

3. Type of Database in SQL Server

There are two types of databases in SQL Server.

1. System Database
2. User Database

System 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:

1. master: It is used to 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.
2. model: It is used as a template for all databases created on the instance of SQL Server.
3. msdb: It 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.
4. tempdb: It 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:

1. Transact-SQL Command
2. SQL Server Management Studio

Here, we will create database using Transact-SQL Command.

4. Database Creating Rules

When we create new database the below rules should be follow.

1. The database name must be unique within an instance of SQL Server.
2. It must also comply with the SQL Server identifier’s rules.
3. The database name has a maximum of 128 characters.

In the below query we have created database named EmployeeDatabase

5. Create Simple Database in SQL Server

The below command is used to create database.

create database EmployeeDatabase

Output

6. Create Database with File Location

create database MyDatabase and specify physical file locations, initial physical file sizes, and autogrowth increments.

CREATE DATABASE [MyDatabase] 
ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\EmployeeDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)

LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\EmployeeDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB) 
GGO 

7. View Created Database

To view database created or not. Ue the below command.

SELECT name FROM master.sys.databases ORDER BY name;  

Output

8. Modify Database

If you want to modify existing database name use the following command.

ALTER DATABASE EmployeeDatabase MODIFY NAME = EmployeeDatabaseModify; 

Output

9. Drop Database

The DROP DATABASE statement allows you to delete one or more databases with the following syntax:

DROP DATABASE  [ IF EXISTS ]
database_name 
[,database_name2,...];

The DROP DATABASE statement is used to drop an existing SQL database.

DROP DATABASE EmployeeDatabaseModify;

Output

10. Drop with IF EXISTS Database

The below command is used to check either database is exists or not to delete database.

DROP DATABASE IF EXISTS EmployeeDatabaseModify 

---

Next