Raymond Lewallen

Sponsors

The Lounge

Syndication

Recent Posts

Tags

View more

News

About Me

MVP

Archives

Advertisement

Raymond Lewallen » The difference in TRUNCATE and DELETE in Sql Server
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
The difference in TRUNCATE and DELETE in Sql Server

I’ve answered this question many times, and answered it again this weekend.  What is the difference when doing a DELETE TableA instead of TRUNCATE TableA?  A common misconception is that they do the same thing.  Not so.  In fact, there are many differences between the two.

DELETE is a logged operation on a per row basis.  This means that the deletion of each row gets logged and physically deleted.

You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.

TRUNCATE is also a logged operation, but in a different way.  TRUNCATE logs the deallocation of the data pages in which the data exists.  The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse.  This is what makes TRUNCATE a faster operation to perform over DELETE.

You cannot TRUNCATE a table that has any foreign key constraints.  You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.

TRUNCATE will reset any identity columns to the default seed value.  This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns.  After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1.  DELETE will not do this.  In the same scenario, if you DELETEd your rows, when inserting a new row into the empty table, the identity column will have a value of 265.

Posted 05-09-2005 5:45 AM by Raymond Lewallen
Filed under: ,

[Advertisement]

Comments

Sahil Malik wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 05-09-2005 7:34 AM
Simple concept + profound implications = good post.
Brendan Tompkins wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 05-10-2005 6:20 AM
Add this to the pile of stuff I didn't know before I met you. Thanks!
Jeffrey Vanneste wrote Daily Links 11
on 05-10-2005 4:48 PM
Thomas wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 05-30-2005 12:09 AM
nice explanation, I was asked this in an interview and couldn’t really explain the difference. thanx….
Greg Low wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 05-30-2005 3:18 AM
And the other big difference is: permissions.

The fact that a user can DELETE rows does not mean they can TRUNCATE the table. By default, they need at least DDL_ADMIN to do that.
Armando Prato wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 07-08-2005 7:04 AM

One other difference to note... delete triggers are not fired when using TRUNCATE.

Excellent post, btw
Alan wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 08-19-2005 2:04 PM
excellent post !!!
Rajiv Bhati wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 03-22-2006 5:41 AM
hi I am Rajiv Bhati, i am a S/w Test professional(QA). the biggest problem that i face while performing testing,is this one i.e basic difference between delete table and truncate table. from your material i got some more information.
thanks & regards
Rajiv Bhati rajivbhati12@gmail.com
shishir wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 05-30-2006 10:18 AM
Gud one!
will help me for my interview :-)
hemant wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 06-01-2006 1:24 AM
Thanks for a great info. It was asked two time in interviw and iwas having no answer
thank for clearing the concept
Srikanth HN wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 06-17-2006 4:20 AM
Thanks !! It helped ..
Short but still easy to understand !
prasadrmarathe wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 06-29-2006 4:17 AM
Thanks,
But what abt RollBack
Is RollBack Possible in both case Delete And Truncate in Sql Server2000
Shakeeb Ahmed wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 07-27-2006 6:36 PM
simple and nice explaination!!
raza ur rahman wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 07-31-2006 2:02 AM
verry good
raza ur rahman wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 07-31-2006 2:04 AM
nice explaination
Nitin Jagga wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 08-08-2006 7:57 AM
Q: Is RollBack Possible in both case Delete And Truncate in Sql Server2000 ?

Sol: Yes, in case of delete you can roll back, but in case of truncate, there's a scenario where rollback is possible. Normally not, you can take truncate as delete + commit as truncate is a DDL Command.

Scenario:

BEGIN TRAN

//DO

TRUNCATE

IF ERROR (OR ANY CONDITION)
ROLLBACK

COMMIT TRAN

in this case, rollback will work even after truncate if it meets your condition.

Other wise, truncate as i mentioned is a DDL Command and will work with auto commit status set as true.

Regards
san wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 09-05-2006 6:25 AM

Its possible to use where condition in truncate table command

Mohammad Iqbal wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 09-06-2006 3:07 AM

Thanks for a nice answer !!

Ramesh Galla wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 09-28-2006 7:18 AM

Thanks...!

shaik afreen wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 10-11-2006 2:09 AM

Thanks for this information

which maks me to understand what exactly happens internally.

Arun Mahendrakar wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 10-17-2006 3:18 PM

Thanks for the info.. very simple words, very concise statements, very lucid explanation

samir wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 11-06-2006 10:05 PM

while inserting data in a table through strored proc and the size of the field is

less than  any valued  ,how we avoid this situation without changing the size of the field

plz mail to

thanks..

samir wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 11-06-2006 10:14 PM

while inserting data in a table through strored proc and the size of the field is

less than  any valued  ,how we avoid this situation without changing the size of the field

plz mail to

thanks..

samir wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 11-06-2006 10:30 PM

what is the use of temporary table ?how we use it?

how to pass dynamic parametyers in stored proc?

while inserting data in a table through strored proc and the size of the field is

less than  any valued  ,how we avoid this situation without changing the size of the field  

plz mail to

thanks..

samir wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 11-06-2006 10:39 PM

sorry,

i am unable to retive my answers.

samir wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 11-06-2006 10:42 PM

sorry,

i am unable to get my answers

samir wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 11-06-2006 10:45 PM

plz.

  inform me in my id

thanks....

Raj wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 12-12-2006 4:49 AM

Excellent

Maqsud wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 01-11-2007 2:30 AM

Hi,

very simple words, excellent post.

sandeep bhardwaj wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 02-02-2007 2:20 AM

Thanks for clearing the concept, before geting this answer i thought that we can not roll back in any how.

thanks again.

sandeep bhardwaj wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 02-02-2007 2:21 AM

Thanks for clearing the concept, before geting this answer i thought that in truncate we can not roll back in any how.

thanks again.

Sunil wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 02-07-2007 3:04 AM

Hi it is really very good artical which is showing the difference between truncate and delete

Zubair Masoodi wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 02-15-2007 7:45 AM

This Post made me to feel better to answer any one who will ask me the difference

AK wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 04-11-2007 4:28 AM

Great Post

Helped me a lot

Risham wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 06-01-2007 6:29 AM

What about the table space concept, will it be freed in both the cases, delete n truncate???

karl wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 06-01-2007 7:52 AM

Somewhat off topic, but...in a MySQL NDBCluster (in memory table-type), a DELETE won't free up the memory used by the table - it'll only make it available for subsequent inserts into it. That is, until the data nodes are re-started.

A truncate will release memory used by a table without the need to restart a data node.

sukhveer wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 06-18-2007 12:15 PM

great job..

sateesh wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 06-28-2007 7:13 AM

Excellent

Suraj wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 07-07-2007 7:32 AM

nice description

kate wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 08-02-2007 11:55 PM

thanks a lot easy to understand.

SRIKANTH wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 08-15-2007 6:03 AM

Very nice and cool explanation.

sathya wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 08-30-2007 5:04 AM

The concepts is good.  The other difference is Truncate will be available for the whole table.  Delete can be done by row level.

Ruchi Kandwal wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 12-20-2007 8:27 AM

it is good to understand.

Hardik wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 01-08-2008 3:57 AM

This is very nice explanation ,,,

jimmy wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 02-20-2008 2:07 AM
shows u do know what the shit is going on actually inside.... good one. thanks.
Biaoge wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 03-19-2008 10:51 PM

It seems that delete( usually follows a where clause) does not release table size even you delete a whole table.

vikram wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 03-24-2008 10:52 PM

thanks for the post ,can we delete a table that has constraints??

vikram wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 03-25-2008 3:28 PM

Hi, thanks for this article , its very easy to understand, i have question, if  we use truncate the data will still present in the pages but they marked as empty for reuse,so what about the space,it wont release any space right?

Rajesh wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 05-20-2008 2:56 AM

Given insight to the concepts. simply nice....

Brijesh Nishad wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 06-10-2008 11:54 AM

Wat a gud explation u have...!!!

It's amazing!!!

really help full for us...

Hantus wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 07-23-2008 11:28 PM

good explain...for better give some  example

sujan wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 09-08-2008 5:04 AM

help ful information

MIke wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 09-12-2008 12:59 PM

Very helpful post.  Short & to the point.  Thanks!!

Joe Gakenheimer wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 09-23-2008 9:21 PM

Nice explanation; simple and concise.

Haresh wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 10-14-2008 12:43 AM

what is the use of temporary table ?how we use it?

how to pass dynamic parametyers in stored proc?

while inserting data in a table through strored proc and the size of the field is

less than  any valued  ,how we avoid this situation without changing the size of the field  

plz mail to  hareshpurohitATtelstra.com

thanks..

Piuesh wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 12-18-2008 6:37 PM

Thanks

hashim wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 01-21-2009 7:18 PM

how i get back truncated table in sqlserver

shahid wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 04-10-2009 5:42 AM

Thanks

Rajender Bhardwaj wrote re: The difference in TRUNCATE and DELETE in Sql Server
on 04-15-2009 8:54 AM

Your tutorial is helpful for me.Thanks to YOU

Add a Comment

(required)  
(optional)
(required)  
Remember Me?