Differences between Delete and Truncate in SQL Server

By:   |   Comments (12)   |   Related: > TSQL


While interviewing SQL Server candidates I find that most are not aware of some of the basic differences between the SQL Server delete and truncate commands as well as whether these operations can be rolled back, so in this tip we will cover some of these aspects.


I have been actively involved in the interview process for over 10 years and have interviewed more than 500 SQL DBAs from junior to senior levels. I follow a simple process when interviewing candidates starting from basic then going through advance topics. I donít expect them to know all of the answers, but they should be aware of the basics and have worked on at least one or two areas like clustering, mirroring, replication, log-shipping, performance tuning, troubleshooting, monitoring, installation, migration, disaster recovery strategy, etc.

If you donít know how SQL Server works you won't know how to handle certain situations where expertise is needed. So I often start with a question like "What is the difference between Delete and Truncate?", which I think is a very basic concept and every DBA must know. To my surprise, I have heard many shocking answers. One candidate told me truncate will delete the entire schema and all tables. I said what? And what about DROP? (silence)

So I decided to write about the differences between DELETE and TRUNCATE with an example, so people can have a better understanding.

Everyone should know that DELETE is DML command and TRUNCATE is DDL command. DELETE deletes records one by one and makes an entry for each and every deletion in the transaction log, whereas TRUNCATE de-allocates pages and makes an entry for de-allocation of pages in the transaction log.

There is also a lot of misconception among people about rolling back after a TRUNCATE or DELETE. People say DELETE can be rolled back, but TRUNCATE canít be rolled back. Is that true, even if we start a transaction? Letís try and find out.

Let's create a table and insert few dummy records for testing:

Empid int NOT NULL,
Name nchar(10) NULL,
City nchar(10) NULL
--Command(s) completed successfully.

insert into Employee values (1,'Shweta','Pune') ,(2,'Stella','Hydrabad') 
-- (2 row(s) affected)
select * from Employee

Original data

SQL Server DELETE with Rollback

Now we have a table with dummy records. Now letís do a DELETE inside a TRANSACTION and see if we can rollback:

--select * from employee
DELETE from Employee where Empid='1'
SELECT * from Employee

We deleted the record where the Empid equals 1 and now we have only one record:

Single Record Deleted

Letís try to rollback and see if we can recover the deleted record:


SELECT * from employee

As you can see below, we have the record back.

Rollback to show the original data

SQL Server TRUNCATE with Rollback

Letís try the same for TRUNCATE:

begin transaction
truncate table Employee 
select * from Employee

Now we have truncated the table and have no records, the table is empty:

All records after a truncate table command has been issued

Letís try to rollback and see if we can get the records back. Run the below command and see what you get:


select * from Employee

As you can see below, we got the records back.

Data rolled back

So we can rollback DELETE as well TRUNCATE if the commands are started inside a transaction and there is no difference between DELETE and TRUNCATE if we are talking about rollback. Try on your own and let me know if you experience any issues.

Differences between the SQL Server DELETE and TRUNCATE Commands

  1. Truncate reseeds identity values, whereas delete doesn't.
  2. Truncate removes all records and doesn't fire triggers.
  3. Truncate is faster compared to delete as it makes less use of the transaction log.
  4. Truncate is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Atul Gaikwad Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, April 14, 2021 - 9:15:15 AM - Jack Back To Top (88533)
It depends on which version of SQL server.

Wednesday, August 31, 2016 - 7:15:58 AM - Atul Gaikwad Back To Top (43236)

Imran, Your point is valid. Most of the sites and even microsoft says Truncate cannot be rolled back but in real if you start under transaction it can be rolled back like delete statement. Let me know if you still have any queries.

Tuesday, August 9, 2016 - 1:39:17 AM - Imran Siddiqui Back To Top (43080)

Hi All,


according to a post in the same website i.e:




in line no 3 and 4 it says "Records removed by the TRUNCATE TABLE statement cannot be restored. "


so i am littile confused about the real point whether it possible or not.


can anyone please confirm it will a proof?






Monday, July 4, 2016 - 7:12:08 AM - Atul Rajaram Gaikwad Back To Top (41810)

Roger: It will last till checkpoint And will be part of your Transaction log backup which help you to recover DB to a specific point in time. 

For More detail please Refer: https://technet.microsoft.com/en-us/library/aa933065(v=sql.80).aspx

Thursday, May 19, 2016 - 11:14:49 AM - Recce Back To Top (41517)

I've got a feeling that back with SQL Server 6.5 it would perform an implicit COMMIT before executing a DDL command.

Or I'm getting confused with how Oracle behaves.

Wednesday, May 18, 2016 - 11:25:57 AM - Tom Brannon Back To Top (41509)

Perhaps some of the confusion comes from the different behavior of TRUNCATE in different systems.  In Oracle, TRUNCATE cannot be rolled back while in SQL Server it can.


Wednesday, May 18, 2016 - 9:56:34 AM - Wayne Back To Top (41506)


I think an important difference is that you can use WHERE and JOIN for selectivity on the DELETE.  Still, very good post.  I hadn't considered that TRUNCATE is DML, but what you say makes sense.

I need to test your statement that TRUNCATE resets IDENTITY seeds, I also need to test to see whether TRUNCATE will follow cascading deletes for foreign key RI.

Wednesday, May 18, 2016 - 8:37:06 AM - Roger Plowman Back To Top (41503)

Here's a question about Truncate. You say it deallocates pages and notes that in the log. Which is all well and good, but how long does the unallocated page *stay* unallocated? Will SQL Server reuse that page and if so, doesn't that mean that eventually the page can no longer be rolled back? Or, rather, restored to a point in time?


Monday, April 25, 2016 - 12:21:47 PM - Greg Robidoux Back To Top (41329)

Hi Rick,

here are some links to other DBA interview questions.




Monday, April 25, 2016 - 12:15:56 PM - Rick Fitch Back To Top (41327)

 Have you ever considered publishing your dba interview questions?
My dba retired and I need to hire a replacement.  The dba resonsibilities are light here mainly BI reporting databases - no transactional stuff - but I don't really know what I'm going to ask prospective interviewees when we start that process.  I've been a sql developer/report writer for many years and have picked up a lot of the dba basics along the way but I definately have holes in my depth of knowledge.
I think there are probably a lot of people in a similar dilemma


Thursday, April 21, 2016 - 8:30:53 AM - ShwetaC Back To Top (41293)

Thanks Atul,for such valuable and understandable article.This blog has really cleared my confusion!!!

Wednesday, April 20, 2016 - 7:39:55 AM - Sagar Upadhyay Back To Top (41281)

In college days - we only get to know from professor that
DELETE command delete all records and TRUNCATE first drop table and recreate it for us, however i was not agree with the theory or profesor.

get free sql tips
agree to terms