Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Differences between Delete and Truncate in SQL Server


By:   |   Last Updated: 2016-04-20   |   Comments (11)   |   Related Tips: More > T-SQL

Problem

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.

Solution

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:

CREATE TABLE Employee
(
Empid int NOT NULL,
Name nchar(10) NULL,
City nchar(10) NULL
) ON [PRIMARY]
GO
--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:

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

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:

ROLLBACK TRANSACTION

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:

ROLLBACK TRANSACTION

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


Last Updated: 2016-04-20


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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 09, 2016 - 1:39:17 AM - Imran Siddiqui Back To Top

Hi All,

 

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

 

https://www.mssqltips.com/sqlservertip/1080/deleting-data-in-sql-server-with-truncate-vs-delete-commands/

 

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 04, 2016 - 7:12:08 AM - Atul Rajaram Gaikwad Back To Top

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

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

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

 

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

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

Hi Rick,

here are some links to other DBA interview questions.

https://www.mssqltips.com/sql-server-tip-category/46/professional-development-interview-questions-dba/

Thanks
Greg

 


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

 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

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

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.


Learn more about SQL Server tools