When delete from table works but truncate table does not
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.
Related Articles
The following articles may also be of interest :
Link back to this article :
https://www.sqlmatters.com/Articles/When delete from table works but truncate table does not.aspx
Keywords
Blog,delete from,truncate table