Truncate and Delete

Question

Post reply

Truncate and Delete

Viewing 9 posts - 46 through 53 (of 53 total)

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

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 1

Truncate is logged. You answered "Delete is logged, truncate is not logged." and therefore you were wrong.

echatzigeorgiou Valued Member Points: 55 More actions · Answer 2

Thanks for your reply Nils, I now realized that truncate is actually logged.

Quoting from: http://www.sqlservercentral.com/articles/delete/61387/

TRUNCATE logs the deallocation of the data pages in which the data exists. TRUNCATE is faster than DELETE due to the way TRUNCATE "removes" rows from the table. It won't log the deletion of each row; instead it logs the deallocation of the data pages of the table. The TRUNCATE statement removes the data by deallocating the data pages used to store the table data and records only the page deallocation in the transaction log. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, "Books Online (BOL)" refers to TRUNCATE operations as "minimally logged" operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.

Tee Time Hall of Fame Points: 3745 More actions · Answer 3

Basics is just what I need. My SQL career is still an infant. 😛

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 4

Interesting and somewhat horrifying - up to now, 43% said delete was not logged, which is amazing considering how much discussion of that there has been on SQLServerCentral.com; and more tha 50% got it wrong that way or some other way. 6% chosing "delete resets ID, truncate does not" is crazy, I hope these guys never use identity columns because if they believe that nonsense they are going to make some really horrible mistakes, like thinking that if their table behaves like a stack IDs will always be contiguous, which can cause lots of lovely bugs.

I too, agree with Tom. The truncate is great if you know what you are doing because it saves time but it is one of those commands that can be horrifying if used without a good understanding of where you expect it to go.

Delete the records in a table, and you can get them back with a good recovery program. Truncate them and you better hope you have a good backup because recovery is not an option. And then there is that truly annoying ID issue...

Jamie

manuel.vazquez-1039821 SSC Veteran Points: 219 More actions · Answer 5

"Faster in performance wise, because it doesn’t keep any logs" ??????

so ?

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 6

So that if you accidently have the wrong server up and you delete records from that server and not the one intended, there is still a path to recovery. Instead of showing the record deleted in the log, it removes the transaction from the log which makes it faster.

Jamie

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 7

I also have posted 3 options and I thought I was right!! Is there an ambiguity to this question and the answers or I really need to go back to the basics :-P:-D:w00t:

(see attached)

Cheers,
John Esraelo