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
