SQL SERVER – What is – DML, DDL, DCL and TCL – Introduction and Examples

SQL commands come in four groups: DML, DDL, DCL and TCL. Here is what each one means, with an example of each, and the one question interviewers love to ask about them.

DML, Data Manipulation Language

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. In one line: DML works on the rows.

Examples: SELECT, UPDATE, INSERT, DELETE, MERGE statements

INSERT INTO Employee (Name, Salary) VALUES ('Pinal', 50000);
UPDATE Employee SET Salary = 55000 WHERE Name = 'Pinal';
DELETE FROM Employee WHERE Name = 'Pinal';
SELECT Name, Salary FROM Employee;

DDL, Data Definition Language

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. In one line: DDL works on the shape, not the rows.

Examples: CREATE, ALTER, DROP, TRUNCATE statements

CREATE TABLE Employee (ID INT IDENTITY, Name VARCHAR(50), Salary INT);
ALTER TABLE Employee ADD DepartmentID INT;
TRUNCATE TABLE Employee;
DROP TABLE Employee;

DCL, Data Control Language

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. In one line: DCL decides who is allowed to do what.

Examples: GRANT, REVOKE, DENY statements

GRANT SELECT ON Employee TO ReportUser;
DENY DELETE ON Employee TO ReportUser;
REVOKE SELECT ON Employee FROM ReportUser;

TCL, Transaction Control Language

TCL is abbreviation of Transaction Control Language. It is used to manage different transactions occurring within a database. In one line: TCL decides where a piece of work starts and whether it counts.

Examples: BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVE TRANSACTION statements

BEGIN TRANSACTION;
    UPDATE Account SET Balance = Balance - 100 WHERE ID = 1;
    UPDATE Account SET Balance = Balance + 100 WHERE ID = 2;
COMMIT;

If the second update fails and you ROLLBACK instead, the first one is undone too. Money does not vanish halfway between two accounts. That is the whole point of TCL in one example.

The interview question: is TRUNCATE DML or DDL?

This one comes up constantly, and it catches people out because TRUNCATE feels like DELETE. Both leave you with an empty table.

TRUNCATE is DDL. DELETE is DML. Here is why the difference is real rather than a naming quirk.

DELETE removes rows one at a time and writes each removal to the transaction log. TRUNCATE does not touch the rows at all. It throws away the pages the table was using, which is why it is so much faster and why the log stays small.

That has consequences you can see. TRUNCATE resets an identity column back to its seed, DELETE does not. TRUNCATE will not fire a delete trigger, because no rows were deleted as far as SQL Server is concerned. And TRUNCATE refuses outright if another table has a foreign key pointing at yours.

One thing that surprises people: in SQL Server, TRUNCATE can still be rolled back if you wrap it in a transaction. Being DDL does not make it permanent. That is true here even though it is not true in every database engine, which is exactly why the question gets asked.

What about DQL?

Some books and some interviewers add a fifth group, DQL, Data Query Language, and put SELECT in it on its own. Others leave SELECT inside DML, which is what I have done above.

Neither is wrong. If you are asked, the safe answer is to give the four, then say that some people separate SELECT out as DQL because it reads data rather than changing it. That shows you know the category rather than just the list.

The quick summary

DML changes the rows. SELECT, INSERT, UPDATE, DELETE, MERGE.

DDL changes the structure. CREATE, ALTER, DROP, TRUNCATE.

DCL changes who is allowed in. GRANT, REVOKE, DENY.

TCL decides whether the work counts. BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVE TRANSACTION.

If you remember only one sentence for the interview, make it this: DML is about rows, DDL is about the shape, DCL is about permission, and TCL is about whether any of it sticks.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.