DDL, DML, DCL and TCL Commands/Statements

Everybody knows about these statements but I just wanted to post this for quick references and also there is a new DML statement (MERGE) available in SQL Server 2008.

SQL commands are instructions which are normally used to communicate with the database to perform specific task and various other functions. Depending on the functionalities we can divide into 4 groups. They are DDL, DML, DCL and TCL.

DDL (Data Definition Language): is a vocabulary used to define data structures in SQL Server. Use these statements to create, alter, drop and truncate data structures in an instance of SQL Server. (MSDN Definition, you can read more here)

  • CREATE : creates an object (a table, for example) in the database.
  • ALTER : database modifies the structure of an existing object in various ways (Eg: adding a column to an existing table)
  • DROP : deletes an object in the database, usually irretrievably.
  • TRUNCATE : remove all records from a table, including all spaces allocated for the records are removed

DML (Data Manipulation Language): is a vocabulary used to retrieve and work with data in SQL Server. Use these statements to add, modify, query or remove data from a SQL Server database. (MSDN Definition, you can read more here)

  • SELECT : retrieves data from one or more tables, or expressions
  • INSERT  : adds rows (formally tuples) to an existing table
  • UPDATE : modifies a set of existing table rows
  • DELETE  : removes existing rows from a table, the space for the records remain
  • MERGE   : insert, update or delete operations on a target table. Normally called UPSERT operation

DCL (Data Control Language): is a vocabulary used to provide security such as roles and permissions in SQL Server. Use these statements to grant and revoke permissions to SQL Server database.

  • GRANT : gives user’s access privileges to database
  • REVOKE : withdraw access privileges given with the GRANT command

TCL (Transaction Control Language): is a vocabulary used to manage the changes made by DML statements in SQL Server. Use these statements to commit or rollback the transaction in a SQL Server database. (Read more here)

  • COMMIT : causes all data changes in a transaction to be made permanent
  • ROLLBACK : causes all data changes since the last COMMIT or ROLLBACK to be discarded, leaving the state of the data as it was prior to those changes.
  • SAVE TRANSACTION : save the state of the database at the current point in transaction

Please Note: I didn’t get a chance to work with ‘Save Transaction’, if you want to read more about this please click here

References:

1. http://culturalview.com/books/sql.pdf

2. http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

3. http://blog.sqlauthority.com/2008/01/15/sql-server-what-is-dml-ddl-dcl-and-tcl-introduction-and-examples/

4. http://msdn.microsoft.com/en-us/library/bb510625.aspx

About these ads

, , , , , , , , , ,

  1. #1 by Leonard Marks on July 18, 2012 - 9:10 pm

    great post

  2. #2 by christi parks on January 30, 2013 - 2:30 am

    Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance…
    would really appreciate help… and Also i would like to thank for all the information you are providing on sql.

Please 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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: