[SQL Server] DDL, DML, DCL, TCL

Learn SQL Server - Parts of SQL – DDL, DML, DCL, TCL

 

SQL originally developed by IBM in 1970’s for their DB2 RDBMS. It became the standard since then. All of the RDBMS today are confirm with ANSI SQL92 standard. SQL Server has added more proprietary functionality to this to come up with new dialect of SQL called as Transact-SQL. Here we will be discussing how T-SQL is divided it’s functionality into modules as per their areas of concern.

T-SQL has main four parts as shown below-

 

 

1. Data Manipulation Language (DML):

This part of the language helps us to manipulate the data from the database system.
These operations include –

Insert Operation - inserting new data into a database table.
Select Operation - retrieving data from one or more database table/views
Update Operation - updating the existing data in a database table depending upon some specified criteria.
Delete Operation – deleting data from a database table on specified conditions.

 

2. Data Definition Language (DDL):

For fast data retrieval, the data itself should be stored in a well structured manner so that it easy to find it out from large storage. For this, SQL Server (and all of the RDBMS) provides data structures to store the data. To create these data structure, SQL provides following commands-

CREATE – to create a data structure like table, procedure, function etc.
ALTER – to change the already existing data structure
ADD – to add sub-elements to data structures like adding column to a table.
DROP - to delete/remove a data structure from the database system.

 

3. Data Control Language (DCL):

Data security is an important aspect of any database management system. SQL provides means for protecting data from unauthorized access. Data security can be achieved by creating database users, roles and giving/revoking them permissions for data structures (here after this referred as database objects). DCL has following commands –

GRANT – gives access to a user/role on specified database object(s)
REVOKE – revokes the access from the user/role for specified database object(s).

 

4. Transaction Control Language (TCL):

The set of SQL statements that should execute to success or fail as a whole is called as Transaction. In set of SQL statements if atleast one statement fails, the database should be restore in a state in which it was before executing the first statement in a set. SQL provides following commands for managing a transaction-

COMMIT - to save the database state after completing the transaction.
ROLLBACK – to restore the database state, in a state before the start of the transaction.