Truncate and Delete

Question

Truncate and Delete

Viewing 15 posts - 31 through 45 (of 53 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Subjective Adapts (8/17/2011)
Truncate
Delete
Faster in performance wise, because it doesn’t keep any logs
Slower than truncate because, it keeps logs
Rollback is not possible
Rollback is possible

Actually Truncate is logged. Also, Truncate can be rolled back.

Here is proof.

http://jasonbrimhall.info/2010/10/11/a-haunting-tsql-tuesday-tale/

Make sure you read Paul Randals post that is linked in that article as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Toreador (8/17/2011)
mbova407 (8/17/2011)
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
Emphasis mine 😉

When truncating - Row actions are not logged. Page deallocations (which is what a truncate does) are logged.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Koen Verbeeck (8/17/2011)
Easy one today, although it seems the 'truncate is not logged'-myth stills seems pretty persistent.

Too persistent -it's like a nasty virus.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

hwells Right there with Babe Points: 732 More actions · Answer 4

Great question. There was a recent SSC article by Prashant Pandey[/url] that did an excellent job explaining the differences. So glad I read that!

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 5

great question and fun reading all the responses today... 😎

Rose Bud Hall of Fame Points: 3147 More actions · Answer 6

Got it right because the "truncate does not log" myth has been beaten out of me by this site, too. I also believe that truncate CAN be rolled back because of a couple QOTDs on the topic. Am I wrong?

Stuart Davies SSCoach Points: 19505 More actions · Answer 7

wware (8/17/2011)
I also believe that truncate CAN be rolled back because of a couple QOTDs on the topic. Am I wrong?

Not wrong on that one - see the script posted earlier in this thread for one example

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Kenneth Wymore (8/17/2011)
Nice question. I knew better than to choose truncate as not being logged because of previous QOTD's and articles regarding this same thing. the truncate does not log myth has been beaten out of me by this site. Thanks SSC! Considering how many have answered incorrectly, it looks like this was a much needed question and that many have learned something today.

Judging by some of the comments, some people who got it wrong are sure (despite being presented with dclear and unambiguous ocumentation) that they got it right. Obviously these people haven't learnt anything (maybe they are incapable of learning?),

Tom

SQLback SSCrazy Points: 2897 More actions · Answer 9

SQLback

SSCrazy

Points: 2897

August 17, 2011 at 2:50 pm

#1369864

Thanks for the question..

Chris Houghton SSCrazy Points: 2557 More actions · Answer 10

Thanks for the question. Straight forward with no smoke and mirrors 😀

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 11

I have a question: how can you execute a T-SQL command if you are not logged

or maybe a better way to address the question would be : What "is logged" means in "Delete is logged"?

I got my answer right by reading the help page and answer elimination: for me, if I don't have the right permission (which is defined by your loggin), I can not remove any data from a table. But after reading the discussion, I think I did not get the definition of the words "is logged" correctly...

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

Logging in this context is not about logins or permissions, it's about write operations that gets logged in the transaction log. Two completely different things.

BuntyBoy SSCommitted Points: 1876 More actions · Answer 13

Back to basics... good:-) question

===========================================
Better try and fail than not to try at all...

Database Best Practices[/url]

SQL Server Best Practices[/url]

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 14

Nils Gustav Stråbø (8/22/2011)
Logging in this context is not about logins or permissions, it's about write operations that gets logged in the transaction log. Two completely different things.

oye!:ermm:

That means there is some commands that are NOT logged in the transaction log... which might be important to know for...? :crazy:

OK... where can I have more basics informations on this "logged" command?

echatzigeorgiou Valued Member Points: 65 More actions · Answer 15

I think the question had multiple correct replies.

I answer:

Delete is logged, truncate is not logged.

Truncate resets the identity for a table, delete does not.

Which I believe is also a correct answer, but I was told that was wrong.