Difference Between DDL, DML And DCL Commands In T-SQL?


Data Definition Language (DDL)

It is used to create, alter, or drop data structures in the database.

1. CREATE – to create objects in the database

2. ALTER – alters the structure of the database

3. DROP – delete objects from the database

4. TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed

5. COMMENT – add comments to the data dictionary

6. RENAME – rename an object

Data Manipulation Language (DML)

It is used for managing data within schema objects.

1. SELECT – retrieve data from the a database

2. INSERT – insert data into a table

3. UPDATE – updates existing data within a table

4. DELETE – deletes all records from a table, the space for the records remain

5. MERGE – UPSERT operation (insert or update)

6. CALL – call a PL/SQL or Java subprogram

7. EXPLAIN PLAN – explain access path to data

8. LOCK TABLE – control concurrency

Data Control Language (DCL)

It is used to control access to data stored in a database.

1. GRANT – gives user’s access privileges to database

2. REVOKE – withdraw access privileges given with the GRANT command

3. DENY – prevent a user from receiving a particular permission

Transaction Control (TCL)

It is used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

1. COMMIT – save work done

2. SAVEPOINT – identify a point in a transaction to which you can later roll back

3. ROLLBACK – restore database to original since the last COMMIT

4. SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s