Difference between Delete and Truncate command in SQL

Today, I am going to discuss about the facts behind the delete query and difference between delete query and truncate query.

Now you will come to know about why truncate query is faster than the delete query and what happens when you fire a delete query on a table.

DELETE

Let me first brief you about delete query, Delete command is used to delete rows from the table you can also filter rows to delete from the table using where clause.

Syntax is shared below:

Delete from users where name = ‘vishal’

Where users is table name and  name is column of the table and using where clause I am filtering rows from users to delete from the table with name vishal.

And if query is fired without any where clause all the rows from the table will be deleted after delete command you need to fire rollback or commit to make the changes permanent or undo it.

Note: this command will cause all the delete triggers to fire on the table.

Truncate

In case of truncate command all the rows from the table are removed and we cannot rollback the changes on the table because no DML triggers are fired on to the table.

Truncate is a DDL command whereas Delete is DML command and truncate is much faster than delete command.

Reason:

When you fire delete command all the data gets copied to Rollback tablespace first then delete operation gets performed that is why when you fire rollback you get data back (the system get it from the rollback table space) . All this process takes time but when you fire truncate command it removes data directly from the table and you cannot retrieve data back from the rollback table space. That is why truncate is faster than delete command.

 

 

 

2 Replies to “Difference between Delete and Truncate command in SQL”

  1. Truncate is auto-commit which causes no copy of data to redundant table hence truncate releases the table space.

    1. Hi Rahul,

      Thanks for your comment but I need to correct your statement.
      Since i mentioned in my post, truncate command belongs to DDL. In DDL, all the queries are fired directly on table unlike DML delete command where trigger runs first on the rollback table space and then we need to write commit; to commit our modification.

      In DML no table space is maintained like rollback table space so no concept of auto commit here.

Leave a Reply

Your email address will not be published. Required fields are marked *