SQL SERVER DML, DDL, DCL and TCL Commands Examples

By: Suresh Dasari Jun 6, 2015

Introduction:

Here I will explain what is the difference between DDL, DML, DCL and TCL commands or statements in sql server with examples. In sql server database operations like insert, update, delete, etc… categorized into multiple operations those are DDL, DML, DCL and TCL. In sql server DDL means data definition language, DML means data manipulation language, DCL means data control language and TCL means transactional control language.

Description:

In previous articles I explained find hierarchical data in sql server, cursor example in sql server, joins in sql server, function example in sql server 2008, Primary key constraint in sql server, foreign key constraint in sql server, query to remove first and last characters from string in sql server and many articles relating to SQL server. Now I will explain difference between DDL, DML, DCL and TCL statements in sql server.

DDL commands in SQL

In sql server DDL means data definition language command. Generally we will use these commands to create or modify existing structure of database objects like create, alter, drop or truncate database objects in database. In sql server following operations will come under DDL operations

CREATE – By using this operation we can create objects (like Tables, procedures, etc…) in database

ALTER – we can modify objects (like Tables, procedures, etc…) in database

DROP – we can drop or delete objects in database

TRUNCATE – It will delete all the records from table and it will reset identity of column to initial value

DML commands in SQL

In sql server DML means data manipulation language command. Generally we will use these commands to perform multiple operations like insert, update, retrieve and delete data in database. In sql server following operations will come under DML operations

INSERT – By using this operation we can insert data in table

SELECT – we can get or retrieve data from table

Delete – we can delete data from table

Update – we can update data from table

DCL commands in SQL

In sql server DCL means data control language command. Generally we will use these commands to secure database objects by creating roles, permissions using GRANT, REVOKE operations. In sql server following operations will come under DCL operations

GRANT – By using this operation we can create access permissions for users to database

REVOKE – we can rollback or take back user access permissions for database

TCL commands in SQL

In sql server TCL means transactional control language command. Generally we will use these commands to perform multiple transactional operations like commit, rollback, save transactions in database. In sql server following operations will come under TCL operations

COMMIT – By using this operation we can save work we done in transactions

ROLLBACK – we can rollback database to original state before COMMIT operation on transactions

SAVE TRANSACTION – we can set save point in transcations