Database Series Part I

Database:

It is an organized collection of structured information or data, typically stored electronically in a computer system. It is usually controlled by DBMS. In general data and DBMS along with the application which is associated with them is referred to as a database system.

DBMS- database management system is an interface between the database and the end-user.

SSMS(SQL Server Management Studio) it is only a client tool that connects the database. It is not server a just software that connects to a database server by using server IP, name and credentials.

What is data?

Data is a collection of a small info unit that is distinct. It can be used in a variety of forms such as text, numbers, media, bytes, etc. It can be stored in pieces of paper or electronic memory, etc. Word ' Data' originates from the word ' datum' which means' single piece of information.'

Evolution of Databases

1. File-Based
2. Hierarchical Data Model
3. Network database
4. NoSQL database
5. Cloud databases
6. Self-driving databases

SQL Server database can be created, drop and altered. For doing this you can use SSMS or simple query.

1) Create a Database Using Query:

Create database databasename

When you create database 2 files were generated which is below

.MDF file- It is a data file that contains actual data.

.LDF file:- It is a log file that means it is a transaction log file which also used to recover the database.

2) Rename Database:

alter database orignal_database_name modify name=new_database_name;


The above query changes the orignal_database_name to new_database_name.

Another way to change the database name by using the system stored procedure.

execute sp_renamedb 'temp1','temp'

The procedure takes 2 input parameter original name and name to change means a new name.



3) Drop or Delete Database:
when you drop the database behind the scene it will delete.MDF and.LDF file which is associated with this database.

Drop database database_name

This will delete database_name database. But one thing to remember when you want to drop the database you will not use this currently or you need to switch another database and then you need to drop a database. Also, make sure that no one can access this database. If any use connected then you need to put a database in single-user mode and then drop this.

The query for this to put a database in single-user mode is

Alter database database_name set SINGLE_USER with rollback immediate

In above query rollback immediate is used for suppose we have 2 users A and B, A is running some query on our database and B want to drop that database so when b fires above query it will forcefully stop user A query execution and revert back it changes immediately if the transaction is incomplete and close that connection.