Creating Altering and Deleting database in SQL Server
In our last article of SQL Server tutorials, we discussed that using SSMS (SQL Server Management Studio), we can connect to the SQL Server database. Here we will learn about creating altering and deleting database in SQL Server.
In SQL Server we are going to interact with 2 types of databases such as
- System databases
- User databases
It contains four databases, those are
It will store all system level information like system id, culture, server id no, server version, server culture, etc
The model database will act as a template for creating new databases under a server environment.
Msdb (Microsoft database):
Microsoft database will store jobs and alerts information i.e. backup file information.
It is a temporary database location which is allocated by the server when the user connected to the SQL Server for storing temporary table information.
Note: Once we disconnected to the SQL Server, the temporary database location will be destroyed automatically.
Note: The above system database is maintained and managed by the system by default.
These databases are created by the user for storing business related information such as employee details, Customer Details, Student details, Product Details, Salary details, etc.
A SQL Server database can be created, altered and dropped in two ways
- Graphically using SQL Server Management Studio (SSMS) or
- Using a Query
To create the database graphically
- Right Click on Databases folder in the Object Explorer
- Select New Database
- In the New Database dialog box, enter the Database name and click OK as shown below.
To create the database using a query
The syntax for creating a database:
Create database <Database Name>
Create database MyDB1
Select the above query and Click on either Execute option or F5 keyboard for execute.
Whether we create a database graphically using the designer or using a query, the following 2 files get generated.
.MDF file -Master Data File (Contains actual data)
This file will store all Tables data and will be saved with an extension of .mdf (master data file)
.LDF file – Transaction Log file (Used to recover the database)
This file will store transaction Query information (insert, update, delete. Create, etc) and saved with an extension of .ldf (log data file)
NOTE: The above two files are used for transferring the required database from one system to another system or from one location to another location.
Root Location of .mdf and .ldf files:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA
To alter a database, once it’s created
Alter database DatabaseName Modify Name = NewDatabaseName
Alternatively, you can also use the system stored procedure
Execute sp_renameDB ‘OldDatabaseName’,’NewDatabaseName’
To Delete or Drop a database
Drop Database DatabaseThatYouWantToDrop
When we drop a database, it deletes the LDF and MDF files.
We cannot drop a database if it is currently in use. We get an error stating – Cannot drop database “NewDatabaseName” because it is currently in use. So, if other users are connected, we need to put the database in single user mode and then drop the database.
Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate
With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the database.
Note: System databases cannot be dropped.
In the next article, I will discuss Creating and working with database tables in SQL Server.
In this article, I try to explain creating altering and deleting database in SQL Server step by step. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.