Oldie but goodie interview question…
What’s the difference between the truncate and delete commands in SQL Server and when might you choose one over the other?
Let’s look at the characteristics of both..
Truncate
- Doesn’t log each individual row deleted but rather logs page deallocations
- Can be rolled back when in transaction
- Can’t always be rolled back in full recovery mode without transaction
- Resets identity seed
- Can’t be used with tables with FKs
- All records removed, can’t include a where clause
- Does not cause delete, instead of or after triggers to fire for each row removed
- Locks whole table
- Requires alter permissions as its considered a DDL statement
Delete
- Logs each individual delete so is slower and takes up more log space
- Can be rolled back when in transaction
- Can always be rolled back in full recovery mode with or without transaction
- Doesn’t reset identity seed
- Can be used with tables with FKs
- All or some records can be removed as where clause can be included
- Causes delete, instead of or after triggers to fire for each row removed
- Locks only specific rows being deleted
- Requires delete permissions as its considered a DML statement
Which one you should choose depends completely on your use case although unless your tables are huge, sticking with delete is the safest due to the nature of what is logged (assuming full recovery mode) BUT…
Truncate CAN be rolled back too
When asked for the difference between truncate and delete commands in SQL Server if you can hit most of the points above the interviewer will be happy. Given that a common misconception is that truncate cannot be rolled back brownie points will be available by pointing out that this is not the case. If the truncate command is issued in a transaction it can be rolled back just like the delete command can. Furthermore even without a transaction it may be possible to ‘roll back’ a truncate too as truncate is a logged command and it doesn’t actually remove underlying data but rather deallocates the space used so it becomes available for re-use. If you act fast and get hold of the .MDF files before the deallocated space is overwritten with the help of certain third party tools you have a good chance of saving your data.
