truncate & delete...newbie question

[tran008]

These two statement seem to be doing the same job when purging the data from table:

truncate table table1
delete table

What is the benefit if any over using them? When should once be uses over an other?

thansk

 

[LNBruno]

Check this out:

http://www.mssqltips.com/tip.asp?tip1080

< M!ke >
Acupuncture Development: a jab well done.

 

[SQLSister]

Truncate is much faster because it is something you cannot recover from the transaction logs. Because this is dangerous in the wrong hands, normally only system admins have the rights to do this.

Delete is slower but can be recovered if someone accidentally deletes. Also the delete statement can have qualifiers so that only a portion of the records are deleted. Truncate will wipe out the whole table no matter what.

I personally prefer not to use truncate on a table unless I can easily recover by re-importing the data or if I want to clean out a table that had test records, so that the real records can be put in instead. Believe me you don't want to accidentally truncate the main table that your whole application is dependent on.

Questions about posting. See faq183-874

 

[onpnt]

I can't find it but maybe someone else can. There was a extensive thread on recovering from delete/truncate transactions. It may be worth searching hard then I apparently can

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[onpnt]

This is one but not the right one. Hopefully you can get the idea on recoverability from a truncate

http://www.tek-tips.com/viewthread.cfm?qid=1298898

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[SQLDenis]

you can only rcover if you did not commit! If you did commit--> update resume

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions