What is – DML, DDL, DCL and TCL in TSQL?

In transact SQL , different type of activity performed ,like we can create database , manipulate date , update, delete even control access to database or database objects.

In some case we also need to secure data or control data so we can restrict unauthorized access. All these activity categorized in four parts.  

DML (Data Manipulation Language)

DDL  (Data definition language)

DCL (Data control language)

TCL  (Transactional Control Language)

We have explained brief all about these, also briefed where and how we can used all these in TSQL.

1. DML (Data Manipulation Language)

It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, DELETE and INSERT statements

The following table lists the clauses that are used in multiple DML statements or clauses.

Clause	Can be used in these statements
FROM (Transact-SQL)	DELETE, SELECT, UPDATE
Hints (Transact-SQL)	DELETE, INSERT, SELECT, UPDATE
OPTION Clause (Transact-SQL)	DELETE, SELECT, UPDATE
OUTPUT Clause (Transact-SQL)	DELETE, INSERT, MERGE, UPDATE
Search Condition (Transact-SQL)	DELETE, MERGE, SELECT, UPDATE
Table Value Constructor (Transact-SQL)	FROM, INSERT, MERGE
TOP (Transact-SQL)	DELETE, INSERT, MERGE, SELECT, UPDATE
WHERE (Transact-SQL)	DELETE, SELECT, UPDATE
WITH common_table_expression (Transact-SQL)	DELETE, INSERT, MERGE, SELECT, UPDATE

2.    DDL (Data Definition Language)

It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

·         ALTER STATEMENTS (TRANSACT-SQL)

·         CREATE STATEMENTS (TRANSACT-SQL)

·         DISABLE TRIGGER (TRANSACT-SQL)

·         DROP STATEMENTS (TRANSACT-SQL)

·         ENABLE TRIGGER (TRANSACT-SQL)

·         TRUNCATE TABLE (TRANSACT-SQL)

·         UPDATE STATISTICS (TRANSACT-SQL)

3.    DCL (Data Control Language)

It is used to create roles, permissions, and referential integrity as well it is used to control access to database objects.(Procedures, Functions, tables, views etc)

 Examples: GRANT, REVOKE statements

GRANT – allows users to read/write on certain database objects
REVOKE – keeps users from read/write permission on database objects

4.    TCL (Transactional Control Language)

It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements.

BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in case of any error