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:
There are some important features of all databases that make them incredibly valuable for managing data.
There are two types of databases in SQL Server.
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:
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:
Here, we will create database using Transact-SQL Command.
When we create new database the below rules should be follow.
In the below query we have created database named EmployeeDatabase
The below command is used to create database.
create database EmployeeDatabase
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)
To view database created or not. Ue the below command.
SELECT name FROM master.sys.databases ORDER BY name;
If you want to modify existing database name use the following command.
ALTER DATABASE EmployeeDatabase MODIFY NAME = EmployeeDatabaseModify;
The DROP DATABASE statement allows you to delete one or more databases with the following syntax:
DROP DATABASE [ IF EXISTS ]
The DROP DATABASE statement is used to drop an existing SQL database.
DROP DATABASE EmployeeDatabaseModify;
The below command is used to check either database is exists or not to delete database.
DROP DATABASE IF EXISTS EmployeeDatabaseModify