The Difference between Truncate and Delete in SQL Server

Question

Post reply

The Difference between Truncate and Delete in SQL Server

Prashant Pandey

SSChasing Mays

Points: 657
More actions
November 22, 2007 at 12:00 am

#184329

Comments posted to this topic are about the item The Difference between Truncate and Delete in SQL Server
Thanks,
Prashant

Viewing 15 posts - 1 through 15 (of 66 total)

You must be logged in to reply to this topic. Login to reply

Martin Bastable SSCommitted Points: 1766 More actions · Answer 1

Martin Bastable

SSCommitted

Points: 1766

November 22, 2007 at 2:04 am

#753325

I learnt something new today 🙂

thanks!

Jwalant Natvarlal Soneji Mr or Mrs. 500 Points: 507 More actions · Answer 2

I too. 🙂

I like the details covered and I'm going to use trucate for all stagging tables used in ssis packages rather then deleting data from them.

Stephen Wilson SSC Veteran Points: 260 More actions · Answer 3

I knew about Truncate, but you have provided useful information about how it differs from DELETE FROM. Thanks

Paul Grubb SSC Enthusiast Points: 124 More actions · Answer 4

I was wondering if you could clear up a issue someone once raised with me.

The chap told me one of the other differences between a Truncate and a Delete relates to the effect on full recovery models.

Is it true that using a truncate against a Full recovery model has a detrimental effect on restores in that the transaction logs are messed up?

Carl Janssen SSC Enthusiast Points: 186 More actions · Answer 5

Thanks for this wonderful article. I learned a lot. and you looked at the differences from many points of view.

I wanted to rate this article a 5 star. I did something wrong with my mouse and Awful was all I could select. I can't modify the rating any more. So the awful is from me and it should mean "awful good"! Thanks for taking your time to write such a good article.

Majdi Yassin-466113 SSC Enthusiast Points: 140 More actions · Answer 6

Realy this is a wonderful article :), it changed my worng ideas about the difference between delete and truncate.

Jai Kumar SSC Eights! Points: 872 More actions · Answer 7

Good one. Before reading this article I my idea was that there is no logging for TRUNCATE tables.

Thanks.

One question from my side

Is this article is applicable for both SQL 2000 and SQL 2005? Or only for SQL 2005?

Mark F-428640 Hall of Fame Points: 3659 More actions · Answer 8

Excellent explanations especially the logging behaviour of both commands. I was hoping I would have saw some details on the behaviour of these commands on temporary tables. Feel free to let me/us know (via a forum post) if you enhance your article with this info as well.

Cheers,

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 9

Jai Kumar (11/22/2007)
Good one. Before reading this article I my idea was that there is no logging for TRUNCATE tables.
Thanks.
One question from my side
Is this article is applicable for both SQL 2000 and SQL 2005? Or only for SQL 2005?

Hi Jai,

This article is applicable to both SQL 2000 and 2005 caz there is no difference between SQL 2000 and SQL 2005 Regarding these TRUNCATE and DELETE commands.:)

Thanks,
Prashant

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 10

Mark Fyffe (11/22/2007)
Excellent explanations especially the logging behaviour of both commands. I was hoping I would have saw some details on the behaviour of these commands on temporary tables. Feel free to let me/us know (via a forum post) if you enhance your article with this info as well.
Cheers,

Hi Mark,

Its very good for me and for all readers if u'll share your knowledge with us.

Please tell me about this,???

Thank you.

Thanks,
Prashant

Jai Kumar SSC Eights! Points: 872 More actions · Answer 11

Thanks for the clarification.

We are expecting more articles like this from you.

mollari_uk Grasshopper Points: 15 More actions · Answer 12

Why do these two behave differently? I don't know, but T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don't want to reset you counter, then delete is there for you.

Just thought I'd chip in with my explaination for this: With delete you aren't always removing all of the rows from the table so you wouldn't want it to reset the counter. Truncate does it the other way because you are always deleting all of the rows (and it gives you the option in contrast to delete).

Mark F-428640 Hall of Fame Points: 3659 More actions · Answer 13

Actually my mistake this does work on temp tables but it does not work on variable tables nor does the drop table command. i.e.

declare @t1 table

(

c1 varchar(10)

)

insert into @t1 values('chess')

select * from @t1

truncate table @t1 -- this will give you an error but using the below commented delete statement

--would work

--delete @t1

select * from @t1

--Drop table statement below also would give you an error if run.

--Drop table @t1

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 14

mollari_uk (11/22/2007)
Why do these two behave differently? I don't know, but T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don't want to reset you counter, then delete is there for you.
Just thought I'd chip in with my explaination for this: With delete you aren't always removing all of the rows from the table so you wouldn't want it to reset the counter. Truncate does it the other way because you are always deleting all of the rows (and it gives you the option in contrast to delete).

Hey mollari, You are right, delete do not resets the counter caz we can also perform the conditional based deletion with delete, But with Truncate we can't.

Thank you,:)

Thanks,
Prashant