When ‘Delete from table’ works but ‘Truncate table’ doesn’t

One of my favourite interview questions is to ask what the difference is between truncate table1 and delete from table1. It’s a good question for a number of reasons and I like it because it gives the interviewee a good chance to talk around the subject and gauge their depth of understanding especially in the area of transaction logging.

Recently a colleague was about to start a round of interviews for a new SQL developer position and he invited everyone in the team to contribute two questions from which he would choose the best for the technical part of the interview. One of the questions put forward was a twist on my question and intrigued me somewhat. The questioner wanted to know when delete from table would work but truncate table wouldn’t. My first thought was permissions but he was actually thinking of constraints.

Both commands will generate an error if it means violating foreign key constraints, however there is a significant difference in this area between the two commands. Delete from will not delete a row if the deletion means a foreign key reference is violated, but truncate table is somewhat less subtle and won’t work if there is a foreign key on the table even if it isn’t violated. This can occur, for instance, if you have already deleted the data in the child table.

If you want to use truncate table in this situation then you will need to drop the constraints, truncate the table and then recreate the constraints.

For completeness there are also other specific circumstances where truncate table won’t work whereas delete from will work, such as tables which have indexed views or are part of replication.