Creating Altering and Deleting Database in SQL Server
In this article, I am going to discuss Creating Altering and Deleting Database in SQL Server with examples. Please read our previous article where we discussed how to connect to SQL Server Database using a client tool called SQL Server Management Studio. As part of this article, we are going to discuss the following pointers.
- Understanding the Different Types of Database in SQL Server.
- Understanding the different mechanisms to create, alter and drop database in SQL Server.
- How to create database graphically?
- How to create a database using the query?
- Understanding the Query to Alter and Drop database in SQL Server.
Different Types of Database in SQL Server.
In SQL Server we are going to interact with 2 types of databases such as
- System databases
- User databases
Understanding the System Databases in SQL Server:
It contains four databases, they are as follows:
Master database: It will store all system-level information like system id, culture, server id no, server version, server culture, etc
Model database: 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.
Tempdb database: 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 you disconnected from the SQL Server, then the temporary database location will be destroyed automatically. The above system databases are maintained and managed by the system by default.
Understanding User Databases in SQL Server:
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. In SQL Server, you can the database can be created, altered and dropped in two ways
- Graphically using SQL Server Management Studio (SSMS) or
- Using a Query
Creating SQL Server 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.
Creating SQL Server Database using Query:
The syntax for creating a database: Create database <Database Name>
Example: 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 am going to discuss Creating and working with database tables in SQL Server with examples. Here, in this article, I try to explain Creating Altering and Deleting Database in SQL Server step by step with examples.