Tuesday, May 23, 2023

Difference between Truncate and Delete in SQL? Example

Truncate and delete in SQL are two commands which are used to remove or delete data from a table. Though quite basic in nature both SQL commands can create a lot of trouble until you are familiar with details before using it. The difference between Truncate and delete are not just important to understand perspective but also a very popular SQL interview topic which in my opinion a definite worthy topic. What makes them tricky is the amount of data. Since most Electronic trading system stores, large amounts of transactional data, and some even maintain historical data, a good understanding of delete and the truncate command is required to effectively work in that environment.


I have still seen people firing delete commands just to empty a table with millions of records which eventually lock the whole table for doing anything and take ages to complete or Simply blew log segment or hang the machine.

Most of the enterprise stock trading system maintains two kinds of database one transactional and other static. Transactional data is a day by day records that need to be purged at the end of data or moved to historical data so that the application can make a fresh start another day. 

If you need to work on such a large set of data, my advice is to get clear and complete knowledge of delete and truncate command, along with their differences and when to use which command to remove data or purge tables.

In this article, we will see where to use truncate in SQL and where to use delete in SQL, How to use truncate or delete and what danger or harm they can create if not used carefully along with the difference between truncate and delete in SQL.



What is the Truncate command in SQL?

Use the truncate table if you need to delete all rows since truncate doesn't allow you to specify the WHERE clause. truncate removes data by deallocating space used by a table which removes a lot of overhead in terms of logging and locking and that's why to truncate is faster than delete.

What you need to take care is a rollback, data deleted by truncate can not be rolled back until data server specifically supports it e.g. MSSQL Server which allows to commit or rollback truncate table statement transactionally. 

Another caveat with truncate table statement is that it doesn't fire a trigger and you can not truncate a table when a foreign key references any column to the table to be truncated. 

The only situation I see which is perfect for using truncate is purging tables with huge data, though there is another solution exists to drop table and recreated it if that makes sense.

And, if you like to understand difference in tabular format, here is a nice table which highlights difference not just truncate and delete but also drop command in SQL. 
Difference between truncate, drop, and delete in SQL



Example of truncate command in SQL

truncate table Orders;  //Order table shouldn't have a column which is foreign key on another table

Difference between truncate and delete command in sql


What is Delete command in SQL?

Delete is another SQL command available for removing records from the table. Delete is even more flexible than truncate like it provides support to WHERE Clause which can be used to remove selective data. 

It logs each row which allows an operation to be rolled back and it also fires triggers. One disadvantage of using delete is speed and locking. Delete acquires a lock on the table and its also very slow operation because of logging, which makes it unsuitable for removing records from large tables. 

One workaround for this is batch-delete in which you remove a batch of records instead on one record at a time. 

Delete is most suitable for removing selective data and use it where you want to roll back the transaction in the database. It’s not useful to purge a large amount of data from tables and should not be used, otherwise, it could lock the table for a very long time, blew log segment, and can take ages to complete.

Example of delete commands in SQL


delete  * from Orders; //delete all row from Orders, should not be used if Orders is large
delete  * from Orders where Symbol="MSFT.NQ" //delete all orders where symbol is MSFT.NQ


Difference between truncate and delete command in SQL

This is an important point to understand before using truncate or delete on the production environment, or writing any script which purges data from tables.

1. truncate is fast delete is slow.
2. truncate doesn't do logging delete logs on per row basis.
3. rollback is possible with delete not with truncate until specifically supported by the vendor.
4. truncate doesn't fire trigger, delete does.
5. Don't delete, truncate it when it comes to purge tables.
6. truncate reset identity column in table if any, delete doesn't.
7. truncate is DDL while delete is DML (use this when you are writing exam)
8. truncate doesn't support where clause, delete does.

So finally if you have table with huge data and want to empty it don’t Delete, truncate it


Interview questions on truncate and delete in SQL

Truncate and delete both are popular interview topics and there is always some question on these commands in SQL interview. Here I am listing some of SQL interview questions based on delete and truncate command in SQL, you can find the answer in this article itself or by google.

1) If you have a table which contains a large amount of data which command will you use for removing data, truncate or delete?

2) What are differences between truncate and delete?

3) Which one is fast truncate or delete?

4) What is the disadvantage of using truncate in SQL?

5) How will you delete data if truncate is not supported and log segment is also not big enough to support complete delete?

6) Is there any way to remove data other than truncate and delete in SQL?

14 comments:

  1. thanks a lot for the information really appreciate you,for spending your time in giving this useful information for many new learners like me.who are new to this field of study.

    ReplyDelete
  2. @ARVIND, thanks for your comment. glad to hear that you like this truncate and delete tutorial on SQL.

    ReplyDelete
  3. difference between truncate and delete is one of the most popular SQL interview question and has been asked many times to many people but more than that if you know real differences between delete and truncate you can avoid mistakes while dealing with large and sensitive data. mostly in report writing, database cleanup, correct use of truncate and delete is must.

    ReplyDelete
  4. GreT post. Seeing the link I was thinking whtz the big deal by it as I already know it but posts always tend to go that extra mile to make it more detailed . Thanks for tht . Also In case you are looking for new topics to blog,request to take a look at the google java interview questions . Questions like how to find matching strings from 2 string arrays If each array has 1 million records etc

    ReplyDelete
  5. One of the important thing to note while using truncate is that it will reset your Identity column if you have any.

    ReplyDelete
  6. Hi Javin,

    When you say truncate would reset the identity column, do you mean the foreign key which was referring to the table that was truncated? Could you explain this with examples.

    ReplyDelete
  7. truncate reset identity column means, if you last id was 100, after truncate it will start again with 1.

    ReplyDelete
  8. database is large and truncate not supported..
    log is not enough.. provide solution for this question plz.. m following this blog since last 1 month by recommendation of my teacher..n really i found much much beneficial and very clearly blog..thnks a lot..

    ReplyDelete
  9. using batch size we can delete rows from the table

    ReplyDelete
  10. wrt the below asked question
    5) How will you delete data if truncate is not supported and log segment is also not big enough to support complete delete?

    would Drop and create be a better option ??????

    ReplyDelete
  11. @Anonymous, Yes, DROP is an alternative, if fits the criterion e.g. resetting of ID columns. When we DROP and recreate a table, ID column will restart from it's first value.

    ReplyDelete
  12. thank you so much for detail interview questions and detail answer for all i really learn a lot plus it really clear my answer for truncate and delete and learn clear difference of both

    ReplyDelete
  13. This Post is awesome. Just adding two more points:

    1. Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.

    2. Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.

    Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm#SQLRF01707

    ReplyDelete