When delete from table works but truncate table does not

Category : Blogs Published : July 20, 2012 User Rating : 4 Stars      Views : 3.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

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


Comments

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered