Post reply

Delete Vs Truncate

  • May 25, 2010 at 9:06 am

    #140511

    I read an article on the difference between delete and truncate operation.

    (1) It said "DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted."

    What does the author mean by "each row gets logged and physically deleted"?

    (2)He also says that "You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.".

    Does he mean that, a row in a table can be deleted even when a foreign key exists?

    (3) He also says that "TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. ". What does he mean by "deallocation of the data pages in which the data exists"?

    I'd really appreciate if someone could explain this in more detail.

    Thanks everyone.

  • May 25, 2010 at 10:29 am

    #1172221

    Truncate will also reset seed values where delete won't

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • May 25, 2010 at 1:52 pm

    #1172328

    I recommend you do some basic SQL courses. Some one may have time to answer these sorts of questions, but if you read the SQL BOL (books on line) or do a course you will get answers to these. The forum is really about solving peoples problems, not giving lessons.

    Leo

    There are 10 types of people in the world.

    Those who understand binary and and those that don't

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • May 25, 2010 at 10:10 pm

    #1172458

    sunny.tjk (5/25/2010)

    I read an article on the difference between delete and truncate operation.

    (1) It said "DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted."

    What does the author mean by "each row gets logged and physically deleted"?

    (2)He also says that "You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.".

    Does he mean that, a row in a table can be deleted even when a foreign key exists?

    (3) He also says that "TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. ". What does he mean by "deallocation of the data pages in which the data exists"?

    I'd really appreciate if someone could explain this in more detail.

    Thanks everyone.

    I agree with Leo... you need to hit the books a bit especially Books Online. If you don't know what that is, please post back. You should probably learn a bit about rows, pages, and extents.

    For your questions, consider how a book is made. For a DELETE, you would be ripping one sentence out of each page of the book at a time and writing down that you ripped out a sentence. For TRUNCATE, you would just throw out the whole book and write down that you threw out the book.

    Guess which would be faster to do?

    Heh... and if you have a "chain" on the book in the form of a foreign key, you won't be able to throw the whole book out, but you can still rip sentences out of it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 26, 2010 at 1:17 am

    #1172498

    check my blog

    http://rknairblog.blogspot.com/

  • May 26, 2010 at 1:36 am

    #1172506

    I think Jeff's expo pretty much explains it in laymans terms as well as you possibly can!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • May 26, 2010 at 12:21 pm

    #1172944

    Jeff, you made it look so simple. Thanks a lot...

  • May 26, 2010 at 2:37 pm

    #1173022

    sunny.tjk (5/26/2010)

    Jeff, you made it look so simple. Thanks a lot...

    Heh... thanks for the feedback, sunny. In truth, it's the only way I can understand this stuff myself. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 26, 2010 at 2:47 pm

    #1173030

    (2)He also says that "You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.".

    Does he mean that, a row in a table can be deleted even when a foreign key exists?

    Ex: Say Items.OrderID is an FK to Orders.OrderID. You can delete a row from the Orders table if it does not have any corresponding Items.

  • May 27, 2010 at 8:13 am

    #1173337

    Jeff Moden (5/25/2010)

    sunny.tjk (5/25/2010)

    I read an article on the difference between delete and truncate operation.

    (1) It said "DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted."

    What does the author mean by "each row gets logged and physically deleted"?

    (2)He also says that "You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.".

    Does he mean that, a row in a table can be deleted even when a foreign key exists?

    (3) He also says that "TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. ". What does he mean by "deallocation of the data pages in which the data exists"?

    I'd really appreciate if someone could explain this in more detail.

    Thanks everyone.

    I agree with Leo... you need to hit the books a bit especially Books Online. If you don't know what that is, please post back. You should probably learn a bit about rows, pages, and extents.

    For your questions, consider how a book is made. For a DELETE, you would be ripping one sentence out of each page of the book at a time and writing down that you ripped out a sentence. For TRUNCATE, you would just throw out the whole book and write down that you threw out the book.

    Guess which would be faster to do?

    Heh... and if you have a "chain" on the book in the form of a foreign key, you won't be able to throw the whole book out, but you can still rip sentences out of it.

    Jeff that is the best analogy I have ever heard on this subject. 😉 You deserve a proverbial pat on the back.

  • May 27, 2010 at 9:58 pm

    #1173703

    Heh... thanks for the feedback. Just don't throw the book at me. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 27, 2010 at 10:24 pm

    #1173709

    Thanks Jeff.That was really nice example and i liked the way u explained simple but really powerful so that noone will forget

  • May 27, 2010 at 11:09 pm

    #1173723

    Ratheesh.K.Nair (5/27/2010)

    Thanks Jeff.That was really nice example and i liked the way u explained simple but really powerful so that noone will forget

    Thanks for the nice compliment, Ratheesh. Heh... like I said, I have to explain it to myself that way so I'm the no one that will forget. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 28, 2010 at 12:50 am

    #1173744

    mean at the time of deletion log file is maintened we can recover data but in the case of truncation we can not recover because it doesn't maintain log file

  • May 28, 2010 at 4:14 am

    #1173813

    Jeff Moden (5/27/2010)

    Just don't throw the book at me. 😛

    No give him a taste of his own medicine and throw a few pork chops instead 😀 :laugh:

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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