Monday 11 March 2013

difference between truncate, delete and drop command in sql?

this is one of the most frequently asked question in interviews

The DELETE command is used to remove rows from a table and DELETE operation is temporary you need to either COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. By using DELETE we can delete only selected rows as per our requirement.

TRUNCATE removes all rows from a table. The operation cannot be rolled back. TRUNCATE is faster and doesn't use as much undo space as a DELETE.
The DROP command removes a table and its related indexes and privileges from the database.The operation cannot be rolled back.
DELETE and TRUNCATE both are DML commands where as DROP is a DDL command.

2 comments:

  1. is truncate a DML command??i think its a DDL command...

    ReplyDelete
  2. Any command which defines or modifies the database structure or schema is called DDL where as DML commands are used for managing data within schema objects.
    In this case TRUNCATE is deleting all the rows from the table, on which it is applied but the table structure is still in the database. since it is only dealing with data manipulation, we can state that as DML

    ReplyDelete