What is Truncate command in SQL
Use truncate table if you need to delete all rows, since truncate doesn't allow you to specify WHERE clause.
Truncate removes data by deallocating space used by table which removes lot of overhead in terms of logging and locking and that's why truncate is faster than delete.
What you need to take care is rollback, data deleted by truncate can not be rolled back unless data server specifically supports it e.g. MSSQL Server which allows to commit or rollback truncate table statement transactional.
Another caveat with truncate table statement is that it doesn't fire a trigger.
You can not truncate a table when a foreign key references any column to the table to be truncated.
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 make sense
truncate table Orders;
What is Delete command in SQL
It logs each row which allows operation to be rolled back and it also fires triggers.
One disadvantage of using delete is speed and locking. Delete acquires lock on table and its also very slow operation because of logging, which makes it unsuitable for removing records from large tables.
Delete is most suitable fore removing selective data and use it where you want to rollback transaction in database. It’s not useful to purge large amount of data from tables and should not be used, otherwise it could lock the table for very long time, blew log segment and can take ages to complete.
1. truncate is fast delete is slow.
Use truncate table if you need to delete all rows, since truncate doesn't allow you to specify WHERE clause.
Truncate removes data by deallocating space used by table which removes lot of overhead in terms of logging and locking and that's why truncate is faster than delete.
What you need to take care is rollback, data deleted by truncate can not be rolled back unless data server specifically supports it e.g. MSSQL Server which allows to commit or rollback truncate table statement transactional.
Another caveat with truncate table statement is that it doesn't fire a trigger.
You can not truncate a table when a foreign key references any column to the table to be truncated.
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 make sense
truncate table Orders;
What is Delete command in SQL
It logs each row which allows operation to be rolled back and it also fires triggers.
One disadvantage of using delete is speed and locking. Delete acquires lock on table and its also very slow operation because of logging, which makes it unsuitable for removing records from large tables.
Delete is most suitable fore removing selective data and use it where you want to rollback transaction in database. It’s not useful to purge large amount of data from tables and should not be used, otherwise it could lock the table for very long time, blew log segment and can take ages to complete.
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 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.
Read full article from Difference between Truncate and Delete command in SQL - Interview Questions with Example
