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
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
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in case of any error
