While working with the database we often need to delete data, sometimes to create more space, sometimes just remove all data to make the table ready for the next day's transaction, or sometimes just selectively remove stale data. SQL provides a couple of handy commands to remove data e.g. truncate, delete and drop. The last one is a bit different because instead of removing data it just deletes the table. What is the difference between truncate and delete command in SQL or when to use truncate vs delete is one of the most frequently asked SQL interview questions?
Knowledge of this critical command is not only useful for interviews but also while working with a big database with a large number of records, especially while writing SQL scripts for purging transaction databases.
An Incorrect choice of SQL command can result in either very slow processor can even blow up the log segment; if too much data needs to be removed and log segment is not enough. That's why it's critical to know when to use the truncate and delete command in SQL.
In this article, we will first see few differences between them and based upon them, you should be able to find out when DELETE is a better option for removing data or TRUNCATE should be used to purge tables.
1. The first and most important difference between TRUNCATE and DELETE command in SQL is that truncate doesn't log row level details while delete is logged. Since TRUNCATE is not logged, it's not possible to rollback it e.g. in Oracle. But some database may provide rollback functionality for truncate, on the other hand, DELETE is always logged and can be rolled back.
2) Due to above reason, TRUNCATE command is much faster than DELETE, and due to same reason, you should not use DELETE to remove large data set, since every delete operation is logged, it may be possible that your log segment gets filled and blew up, if you try to empty a large table using DELETE command.
3) One syntactical difference between DELETE and TRUNCATE is that former is a DML command while later is a DDL command.
4) Another critical difference between TRUNCATE and DELETE command in SQL is that former reset any Identity column while DELETE retains the value of identity column.
5) DELETE command work at row level and acquire the row level lock while TRUNCATE locks the whole table, instead of individual rows.
6) One more difference between TRUNCATE vs DELETE comes from the fact we discussed in the first option, it doesn't activate the trigger, because it doesn't log individual row deletion, while DELETE activates the trigger, because of row level logging.
7) Truncate is usually used to remove all data from tables like in order to empty a particular table and you can define any filter based upon WHERE clause, but with DELETE, you can define the condition in WHERE clause for removing data from tables.
8) Since TRUNCATE is a DDL operation, it's automatically get committed, on the other hand, DELETE is not auto-commit.
In this article, we will first see few differences between them and based upon them, you should be able to find out when DELETE is a better option for removing data or TRUNCATE should be used to purge tables.
TRUNCATE vs DELETE in SQL
As I said both commands are used to remove data in SQL, DELETE is more common than TRUNCATE as it's part of the essential CRUD (Create, Read, Update, and Delete) operation, but there are subtle differences between them. Let's see them first :1. The first and most important difference between TRUNCATE and DELETE command in SQL is that truncate doesn't log row level details while delete is logged. Since TRUNCATE is not logged, it's not possible to rollback it e.g. in Oracle. But some database may provide rollback functionality for truncate, on the other hand, DELETE is always logged and can be rolled back.
2) Due to above reason, TRUNCATE command is much faster than DELETE, and due to same reason, you should not use DELETE to remove large data set, since every delete operation is logged, it may be possible that your log segment gets filled and blew up, if you try to empty a large table using DELETE command.
3) One syntactical difference between DELETE and TRUNCATE is that former is a DML command while later is a DDL command.
4) Another critical difference between TRUNCATE and DELETE command in SQL is that former reset any Identity column while DELETE retains the value of identity column.
5) DELETE command work at row level and acquire the row level lock while TRUNCATE locks the whole table, instead of individual rows.
6) One more difference between TRUNCATE vs DELETE comes from the fact we discussed in the first option, it doesn't activate the trigger, because it doesn't log individual row deletion, while DELETE activates the trigger, because of row level logging.
7) Truncate is usually used to remove all data from tables like in order to empty a particular table and you can define any filter based upon WHERE clause, but with DELETE, you can define the condition in WHERE clause for removing data from tables.
8) Since TRUNCATE is a DDL operation, it's automatically get committed, on the other hand, DELETE is not auto-commit.
Summary
Here is a nice table to remember the difference between truncate and delete command in SQL
That's all on the difference between TRUNCATE and DELETE command in SQL. As we learned, you should truncate if you want to completely empty your tables, if truncate is not what you want then, you should look at the batch delete option. Batch delete is actually a little faster than deleting individual rows and also doesn't blew log segment.
Related SQL Interview Questions with Answers
Difference between Primary key and Candidate key in SQL
Difference between Clustered Index and Non-Clustered Index in database
Difference between View and Materialized View in database
Difference between Correlated and Noncorrelated subquery in SQL
How to join multiple tables in single SQL Query
Hi there, You can also use DROP command to purge database and recreate it. In my interview, I was asked difference between DROP and TRUNCATE command? My Answer was, if you DROP table, than all table structure along with data will be removed e.g. access granted, integrity constraints etc. When you use TRUNCATE command, it only removes data and keep structure of table intact. let me know if this is correct
ReplyDeleteYes, although Drop and Truncate both are DDL statements, Drop takes out the complete data along with the structure where as Truncate will remove only the data from the table
DeleteDelete and Truncate both can be rolled back when used with Transaction.
ReplyDeleteIf Transaction is done, means committed then we can not rollback Truncate command, but we can still rollback Delete command from Log files, as delete write records them in Log file in case it is needed to rollback in future from log files.
If you have a Foreign key constraint referring to the table you are trying to truncate, this won't work even if the referring table has no data in it. This is because the foreign key checking is done with DDL rather than DML. This can be got around by temporarily disabling the foreign key constraint(s) to the table.
If accidentally you removed all the data from table using Delete/Truncate. You can rollback committed transaction. Restore the last backup and run transaction log till the time when Delete/Truncate is about to happen.
Here is the link related to this topic
http://wpzone4u.blogspot.in/2014/08/difference-between-delete-truncate-in.html
Sql Query to Rollback Table after Truncate Command.Here is the link
http://wpzone4u.blogspot.in/2014/08/rollback-table-after-truncate-command.html
Truncate reset HWM (hight water mark) but DELETE doesn't
ReplyDelete