Difference between DELETE and TRUNCATE in SQL Server

               The most frequently asked technical interview question in sql server is What is the difference between Delete and Truncate. When the interviewer asked this question, we feel as this is very basic question.  but when you try to give answer then you feel as it is some logical question. But you are not able to give exact answer.

                So to give exact differences between delete and truncate, you should know when we use delete? and when we use truncate?

Also Read:
SQL Query To Get Highest Salary Employee Details In A Employee Table

                Now we will discuss what are the differences between delete and truncate.

Basic differences between delete and truncate:

1. Delete and truncate both are used to delete the data in a table, but here we can use WHERE condition in DELETE Statement, we can’t use WHERE condition in TRUNCATE Statement.

           Right? We all know this answer. When the interviewer asked the question tell me the difference between Delete and Truncate, then we will immediately give this answer. 90% of candidates will give this answer.

Immediately the next question from interviewer is “what else differences apart from this”.

2. Delete is a DML Command and TRUNCATE is a DDL Command.

          This is also one difference. But this answer is like “question to answer in an Exam” not a real time answer. As a fresher this answer is wrathful but as experienced not that much of exact answer what the interviewer expect. He still expects some more from you.

Also Read: How To Get Top 2 Highest Salary In a SQL Server

3. Rollback is possible in DELETE and not possible in TRUNCATE. (We cannot rollback TRUNCATE command, only if Transaction is done, means COMMITTED. Otherwise we can rollback TRUNCATE command also)

         This is a good answer, but the next immediate question from Interviewer is “How can you Rollback data?”. You should be ready to give answer for this question. If you want answer for this question, enter your Mail ID here I will send you the answer.(30% of candidates will give this)

4. Performance wise Truncate is good. DELETE slower than truncate because, it keeps logs. Truncate doesn’t keep any logs.

         This answer is good as a Fresher and Experienced. Only 15% of candidates will able to give this answer. If you are able to give this answer, then it makes good impression on you.

Note: Only 10% of candidates will give all the difference what I mentioned above.

Also Read: Tricks and Tips to Solve Aptitude Problems

5.       There is one more difference with IDENTITY column. You should explain it with one example.
           If we have two table with 10 records in each table. In that we have ID column with IDENTITY. (we all know IDENTITY columncreates a numeric sequence for you. You can specify a column as an identity in the CREATE TABLE statement)

Syntax of IDENTITY Data type:

CREATE TABLE Table_Name(Column1 INT IDENTITY(1,1), Column2 INT)

                So in both tables we have one Identity column. Now you delete the First table by using following command

Delete from Table1

                Now insert new record. The new record value is 11.

Now you delete the Second table by using the following command

Truncate table Table2

                Now insert a new record. The new record value is 1.

I hope you understood this difference between Delete and Truncate.

Conclusion: If you able to explain these differences to interviewer, then 99% of chances are there to get selected. Because most of the interviewers also doesn’t know all these differences. If you know any more differences or if you have any doubts/queries on “Differences between DELETE and TRUNCATE” then comment here, I will get back you as soon as possible.

Note: Bookmark (Ctrl+D) this page as “Delete and Truncate”, then it will easy to you for revision at the time of interview.

Freshers Junction blog will give more SQL Server technical interview questions and Jobs updates via email. To get all updates via Gmail, Subscribe for our News Letter.

Subscribe here and get all updates

More Related Interview Questions:

1. What is the difference between Where clause and having clause in SQL Server

2.  How to copy entire table into another table in SQL Server

3. SQL Query to get Department wise Maximum Salary Employee Details from SQL server Table