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

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

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.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

155 thoughts on “SQL SERVER – What is – DML, DDL, DCL and TCL – Introduction and Examples

  1. Hi Mr.Dave,
    I am so impressed with ur resume, and I wish u all success,
    I am new to MS SQL server and I am looking for an example that can show me how to use DDL statements of MS SQL server for how to create a table (for example):
    Table name= Sales
    Sales ( CustomerNo, ItemNo, Price, quant, TotalAmount )
    Primary key ( CustomerNo, ItemNo)
    Foriegne Key (CustomerNo)
    Constraint ( quant in sales table must be = < item balance in inventory table ).

    I would highly appreciate ur kind info

    Many thanks and regards

    Adnan

    Like

  2. What about DECLARE and SET? I read posts about not mixing DDL and DML due to stored procedure recompiles, but while these seem like structure and therefore DDL, variables are so temporary that I’m not sure.

    Thanks!

    Like

  3. Hi Pinal
    I would like to know about the commant invoke and how to use this command in sqlserver with one example.
    Thanks&Regards,
    Shiva

    Like

  4. This artical is useful to know about the sql language types.

    I know to use the DDL and DML but want to know TCL please do post some easy code.

    Like

  5. Dear Sir

    Hope, will be better and safe and sound, when i watched and read your website completely,it is too dramatic , mavel and i have no words in skull to praise you.

    Please sir i have learnt the DML, and other but do post a simple code of TSQL or TCL

    Respectfully yours
    Bakht Riaz

    Like

    • This is Krishna prasad

      Sql is a query language that is used to interact with database …
      in that we have sub parts
      ddl
      dml
      tcl
      dcl
      so ddl is the part in sql

      ddl (data definition or description language)
      has some commands
      Create,
      alter
      Drop

      Like

  6. Dear Dave,

    Can you pls elucidate the difference between MS-SQL and ORACLE database?

    I heard that Oracle has more features for “Customization” and “Security” from my fellow associates

    Thanks

    Jetty

    Like

  7. To answer to post reply 11, the difference between SQL and DML.

    First, I guess you got to know that SQL acronym stands for Structured Query Language.

    Second, DML means Data Manipulating Language.

    There is no difference between those two as DML is a statement category of SQL. In other words, DML is part of SQL.

    This is only because SQL is somehow divided into categories, and those categories includes DDL (Data Definition Language), DML (Data Manipulating Language), DCL (Data Control Language) and TCL (Transaction Control Language). All these categories forms SQL (Structured Query Language).

    I hope this helps.

    Thanks!

    Like

  8. I am trying to convert a SQL Server database to DB2 and was wondering if there is any way to easily get the DDL for the database or transfer data. I expect the data extract/transform/load process will require some manual intervention, but getting the DDL seems like it should be built in to SQL Server functionality.

    Like

  9. I got the following question in an assignment and was unable to get it all right. It reads “Explain six components of SQL”

    I’ll highly appreciate ur response

    Like

    • 1. One SQL-agent responsible for causing the execution of SQL statements. It is usually an application program that calls one or more externally-invoked procedures in an SQL-client Module.

      2. One SQL-implementation; a database management system (DBMS) that executes SQL statements. Your SQL-agent considers your DBMS to have two components: (a) one SQL-client, to which the SQL-agent is bound, and (b) one or more SQL-servers to manage your SQL-data. (SQL-data consists of the descriptions of all the SQL Objects, plus all the data values you can access with your DBMS.) The SQL-client is the part of your DBMS that establishes connections to the SQL-servers; it maintains a diagnostics area and other state data that relate to the interactions between the DBMS and the SQL-agent. Each SQL- server has three responsibilities: (a) it manages the SQL-session taking place over the SQL-Connection between itself and the SQL-client, (b) it executes SQL statements received from the SQL-client, receiving and sending data as required, and (c) it maintains the state of the SQL-session, including the and certain session defaults. The method of communication between the SQL-client and the SQL-server(s) is implementation-defined, but the Standard does specify how an SQL- agent will communicate with your DBMS (see SQL Binding Styles).

      3. Zero or more SQL-client Modules, each containing zero or more externally-invoked procedures. SQL-client Modules are programming modules — exactly one is associated with an SQL-agent at any time.

      4. Zero or more s. An SQL , or authorization identifier, represents a user of SQL-data.

      5. Zero or more Catalogs.

      6. Zero or more sites (e.g., Base tables) that contain SQL-data.

      Like

  10. I notice there are a lot of basic questions here that I could help with. Where do the answers to these questions get posted? Does the author reply to you all individually?

    Like

    • Hello Eric,

      Many questions, I have answered in personally and many I have posted on forum.

      Eric,

      It will be really good if you can help out on this forum by answering people with their question.

      Kind Regards,
      Pinal

      Like

  11. Pingback: SQL SERVER - Example of DDL, DML, DCL and TCL Commands Journey to SQL Authority with Pinal Dave

    • Hi Rizal,

      plz go thru the below

      select distinct(emp_id) from the department
      where first_name like ‘ra%’

      It contains all keywords which u want to clear.

      Thanks

      Like

    • DML:- DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE statements
      DDL:- DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME statements
      DCL:- 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. Examples: GRANT, REVOKE statements
      TCL :- TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database. Examples: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION statements

      Like

  12. DDL(Data Defination Language):
    DDL is used to create the structure of the table. DDL commands are CREATE —Used to create any objects(Table,View,Trigger,Stored procedure) in the database. ALTER – Used to modify the objects in the database.DROP –this command used to drop the objects with the structure.TRUNCATE–This command also used to delete the objects. RENAME–used to modify the object name in the data base.

    DML(Data Manipulation Language):
    DDL commands used to create the objects, DML commands used to manipulate the objects. DML commands are INSERT–This command is used to insert the records into table, UPDATE–This command is used to update the record which is already exisiting in the table, DELETE– This command is used to delete the record which is already existing the table. It is deletes only records not the structure.

    DRL(Data Retrivel Language):
    DRL Command used to retrive the datafrom the database.
    SELECT command used to retrive the data.

    DCL(Data contole Language):
    DCL commands used to security purpose.
    GRANT–this command used to give the pemissons to the paricular Data.
    REVOKE–This command used to release the permissions, which already given to any user.

    Like

  13. hi ,
    am a learner want to ask is there chance to update the number of rows by using update statement in single attempt ? pls give a solution for this question,…..

    Like

  14. hi,
    i mean if we want to update column1 and column2,column3 data,all this three columns we want to update in diffirent data variations how can we get this in a single attempt with using update statement it is possible? waiting for ans

    Like

  15. DR : Select.
    DML : Insert, Update, Delete, Merge.
    DDL : Create, Alter, Drop, Rename, Truncate.
    TC : Commit, Rollback, Savepoint.
    DCL : Grant, Revoke.

    Like

  16. In DDL we use
    DDL
    CREATE – Creates objects in the database
    ALTER – Alters objects of the database
    DROP – Deletes objects of the database
    TRUNCATE – Deletes all records from a table and resets table identity to initial value.

    DML
    SELECT – Retrieves data from a table
    INSERT – Inserts data into a table
    UPDATE – Updates existing data into a table
    DELETE – Deletes all records from a table

    DCL
    GRANT – Gives user’s access privileges to database
    REVOKE – Withdraws user’s access privileges to database given with the GRANT command

    TCL
    COMMIT – Saves work done in transactions
    ROLLBACK – Restores database to original state since the last COMMIT command in transactions
    SAVE TRANSACTION – Sets a savepoint within a transaction

    Like

  17. hi shirisha

    Difference between Drop,Delete and truncate is

    Drop- Drop command will delete the entire row also the structure.it is DDL command.

    Delete- Delete command only remove the data from the table.it is DML command. You can Rollback.

    Truncate- truncate will delete the contents not the strucure. it is DDL command.In truncate you cant rollback.it removes record permanently.you cant use where clause in Truncate.

    Like

    • Difference between Drop and Delete are as follows:

      1) DROP is a DDL statement in order to drop the table and database:

      i) SYNTAX:

      DROP TABLE TABLE_NAME;
      DROP DATABASE DATABASE_NAME;

      EXAMPLE:

      DROP TABLE EMP_DEPARTMENT;
      DROP DATABASE EMPLOYEE

      ii) When we used DROP TABLE it deletes the entire table data with a structure(column names also).

      iii) When we used DROP DATABASE it deletes the entire DATABASE containing a number of tables, functions, stored procedures …………………n .

      2) DELETE:

      i) Delete command is used to delete the data within a table and not for any database.

      ii) When we use Delete command it only deletes the data within a table but keeps the structure of table as it is.

      SYNTAX: DELETE TABLE_NAME

      EXAMPLE:

      DELETE EMPLOYEE;

      (That is it kees the table name as it is)

      I hope , after reading this you would be satisfied.

      Like

  18. hello, i am Bivash Kumar Singh. i am complite Gniit from kolkata, but i am not so confedent in sql so what should i do

    Like

  19. hi guys.. i have one doubt?
    tcl means transcational control language..?
    dtl means data transcation language?
    what is the difference between these two?
    in tcl they told only committ and rollback.. they are not mentioned the svae point in the tcl.. but dtl they mentioned…
    what is the exact difference between thes two?

    Like

  20. DDL (Data Definition Language): CREATE, ALTER, DROP
    DML(Data Manipulation Language) : INSERT, UPDATE, DELETE
    DCL (Data Control Language) : GRANT, REVOKE
    TCL (Transactional Control Language) : COMMIT, SAVE POINT, ROLLBACK
    DQL (Data Query Language) : SELECT

    Like

  21. hello every one this is EKRAR and i want your help

    actually i want to create a whole database for practice but i dont have any idea that how many table should be in that and what should be the name of thats table and what type of data should be in that

    so any one is here who can help my
    i really needy for your suggestion

    and i am waiting for your suggestion at
    [email removed]

    Like

  22. Hi Mr. Pinal Dave,

    I am Prasanna – learner and new at SQL
    can you please explain that how do I takkle the problem below.

    I have three tables A, B and C

    A – Temperory data
    B – All Raw data
    C – History Data

    please note that the table structure and column names are same in all three tables.
    I have updated data in table A.
    I already have some old data in table B.
    Table C is empty.
    Now I want to append the updated data (records) from table A to table B. However there may be duplicate records found while appending the data. I dont want to delete duplicate data(records) found from table B. I just want to move that records from table B to table C and append the updated data from table A to table B. The first column is Identicle in all three tables –
    Note :- The first column is SO Number and there are 1000 SO number which are 5 digits number and there may be multiple records under 1 particular SO.

    Please tell me the stored procedure for this.

    Thanks & Regards,
    Prasanna

    Like

  23. I am a student at klu (vijayawada) i cant understand what my professor is teaching but i am familier with SQL as i m reading u r posts….
    THANKS A LOT

    Like

  24. Hi Pinal Dave,

    I am new to MS Sql server. I want to know the command used to describe the table ,since DESC is not working with Ms SQL Server.

    also I would like to know sql querys for following scenarios

    Owner of the company wants to create 5 users,3 in following fashion
    1) 2 -Must have authority of admin
    2) 2- Normal user
    3) 1 – User whose password is blocked for 15 days

    Regards,
    kappy

    Like

  25. SELECT is a DRL statement.

    DRL ::

    DRL means Data Retrieval Language. This will be used for the retrieval of the data from the database.
    In order to see the data present in the database, we will use DRL statement. We have only one DRL
    statement.

    SELECT is the only DRL statement in SQL

    Like

  26. Hi Pinal
    Wishing You a Very Very Happy New Year 2013.
    great Person SQL Guru
    I am new to started My SQL study , Please help me when I require.

    Like

  27. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

  28. TCL

    Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
    COMMIT – save work done
    SAVEPOINT – identify a point in a transaction to which you can later roll back
    ROLLBACK – restore database to original since the last COMMIT
    SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use

    Like

  29. Great post. I was checking constantly this weblog and I’m inspired!
    Extremely useful information particularly the
    closing part :) I care for such information a lot. I used to be seeking this particular information
    for a very lengthy time. Thank you and best of luck.

    Like

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