TRUNCATE vs DELETE in SQLAs I said both commands are used to remove data in SQL, DELETE is more common than TRUNCATE as it's part of essential CRUD (Create, Read, Update and Delete) operation, but there are subtle differences between them. Let's see them first :
1) 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 get 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 value of identity column.
5) DELETE command work at row level and acquire row level lock while TRUNCATE locks whole table, instead of individual rows.
6) One more difference between TRUNCATE vs DELETE comes from the fact we discussed in first option, it doesn't activate trigger, because it doesn't log individual row deletion, while DELETE activates trigger, because of row level logging.
7) Truncate is usually used to remove all data from tables e.g. in order to empty a particular table and you can define any filter based upon WHERE clause, but with DELETE you can define 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.
That's all on 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 that you want than, you should look at batch delete option. Batch delete is actually 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