Creating Altering and Deleting Database in SQL Server

Different Types of Databases in SQL Server.

Two different kinds of databases, including System databases and user databases, will be interacted with in SQL Server.

SQL Server's system databases:

System databases are those that are made and controlled by SQL Server itself. As seen in the graphic below, SQL Server contains four system databases.

Let's talk about each of the databases above's roles and responsibilities.

Principal database Everything related to the system, including the system ID, culture, server ID number, server version, and server culture, is kept in this database.

Sample database: In a server context, the model database will serve as a template for building new databases.

Microsoft database, or MSDb: This database will hold information about jobs and notifications, such as backup file information.

Tempdb database: The server creates a temporary database location known as the "tempdb database" when a user connects to the SQL Server to store temporary table data.

NOTE: The temporary database location will be immediately deleted after you disconnect from the SQL Server. By default, the system manages and maintains the system above databases.

User Databases in SQL Server:

In SQL Server, databases created and maintained by users are referred to as user databases. These databases store information about employees, customers, students, products, salaries, and other business-related details.

There are two methods in SQL Server for creating, modifying, and deleting user databases.

• Graphically using SQL Server Management Studio (SSMS) or
• Using a Query

Creating SQL Server Database Graphically:

• Using a query or graphical means with SQL Server Management Studio (SSMS)
• Using the Object Explorer, right-click the Databases folder and choose "New
• Database" to create a SQL Server database graphically.
• Enter the database name in the New Database dialogue box and press the OK button, as indicated in the image below.

The database will be created as indicated in the image below as soon as you click the OK button.

How to create an SQL Server Database using Query?

The SQL Server database creation syntax is as follows: Build the

Example: Create database TestDB

After choosing the query above, use the F5 key or the Execute option to start it. Whether we construct a database using a query or graphically with the designer interface, the following 2 files are created.

.MDF file: The Master Data File (.MDF file) holds real data. It will be saved with the extension.MDF (master data file) and contains all of the data from the tables.

.LDF file: The Transaction Log file (.LDF file) is utilised for database recovery. The insert, update, and delete transaction query data will be kept in this file. Create, etc.) and store it with the log data file (.ldf) extension.

NOTE: The two files mentioned above can be used to move the necessary database from one machine to another or from one location to another.

How to Rename a database in SQL Server?

• After creating a database, you can use the Alter command to change the database's name, as demonstrated below.
• Modify the database Modify Name of DatabaseName = NewDatabaseName
• As an alternative, you can modify the name by using the system-defined stored method that follows.
• Run the sp_renameDB command. "NewDatabaseName," "OldDatabaseName,"
• In SQL Server, how may a database be dropped or deleted?
• The DROP command must be used in SQL Server in order to drop or destroy a database.
• Drop the database that you wish to remove.
• SQL Server internally erases the LDF and MDF files if a database is dropped.

A message reading "Cannot drop database "DatabaseName" because it is currently in use" will appear if you try to drop a database while it is in use. Therefore, you must first place your database in single-user mode and then dump it if other users are connecting to it. To put the database in single-person mode, use the command that follows.
All pending transactions are rolled back when the Rollback Immediate option is selected, closing the database connection.

NOTE: Dropping system databases is not possible.

With SQL Server, you may use SQL commands or the Microsoft-provided SQL Server Management Studio (SSMS) graphical interface to carry out tasks, including creating, modifying, and deleting databases. The fundamental SQL instructions needed to do these tasks are listed below:

1. Creating a Database: To create a new database, you can use the established DATABASE statement and the intended database name.

2. Modifying a Database: A database can be modified to change its name, configure options, or add or remove filegroups, among other things. However, only some components of a database can be changed once it is created.

To rename a database, for example,

For example, to rename a database:

3. Delete a Database: A database can be deleted with the DROP DATABASE assertion, which is visible via the database name. Continue inside the database as dropping it will entirely remove all of the data and objects within.

For Example:

NOTE: Since creating, modifying, and deleting databases usually require administrative capabilities, make sure you have the right permissions to run these commands.

You can carry out these actions via the graphical user interface of SQL Server Management Studio (SSMS) by connecting to your SQL Server instance, right-clicking on the "Databases" node in Object Explorer, and choosing the relevant choice from the context menu.

These procedures are essential to keeping an efficient and neat database environment.

Creating a Database: The CREATE DATABASE statement creates new databases. This statement allows administrators to specify different parameters, including file locations, sizes, and growth options. Certain parameters must be carefully considered to optimise resource allocation and database performance.

Modifying a Database: You can easily change a database's name, filegroups, and recovery mechanism by using the ALTER DATABASE statement. Administrators can improve database efficiency and respond to changing requirements with this functionality.

Database deletion: Administrators can permanently remove databases from the SQL Server instance by using the DROP DATABASE statement.

This command will permanently delete all related data and objects; therefore, proceed with caution.

Conclusion:

In conclusion, establishing, modifying, and removing databases are just a few of the crucial operations involved in managing databases in SQL Server. Additionally, database maintenance activities may be executed more easily by administrators thanks to SQL Server Maintenance Studio's (SSMS) user-friendly graphical interface.

In summary, careful planning, a thorough grasp of database architecture, and adherence to best practices are necessary for the efficient management of databases in SQL Server. By implementing these principles, administrators may guarantee the availability, integrity, and security of their database environments.