Introduction to Database in SQL Server

This blog defines database and the types of TSQL commands in SQL Server.

Database

Database in SQL Server is maintained with the help of types of files called as Data File and Log File.

Where

Data File - It is used for storing the data
Log file - It is used in managing the transaction

1. A Database requires minimum of one data file and one log file
2. Data Files are divided into two files,
   
   • Primary Data .MDF
   • Secondary Data .NDF
    
3. Here, the primary data file, which contains the system, requires information about the database and it also contains the user data
4. Secondary data only contains the user data
5. The Primary data is saved with .MDf (Master data File)
6. The secondary data file is saved with .NDF (Next Data File)
7. The log file is saved with .LDF (Log Data File)

Types of TSQL Command

All the commands in the TSQL are classified into the following 5 categories,

1. Data Definition Language (DDL)
   
   All the commands of TSQL, that are used to create objects, alter the structure of the objects and delete the object collectively called as DDL.
   
   
   • CREATE
   • ALTER
   • DROP
   • TRUNCATE
      
2. Data Manipulation Language (DML):-
   
   All the commands of TSQL that are used to store the data into the database, modify the database and delete the data, are collectively called as DML.
   
   
   • INSERT
   • UPDATE
   • DELETE
    
3. Data Query Language (DQL):-
   
   All the commands of TSQL that are used to request data from the database are called as DQL
   
   
   • SELECT
    
4. Data Control Language (DCL):-
   
   All the commands of TSQL that are used to allow the user to access the data or restrict the user from accessing the data are collectively called as DCL.
   
   
   • GRANT
   • REVOKE
   • DENY
    
5. Transaction Control Language (TCL)
   
   All the commands of the TSQL that are used to create and manage the transaction are collectively called as DML.
   
   
   • Begin Transaction
   • Commit Transaction
   • Rollback Transaction
   • Save Transaction