Post reply

delete without transaction

  • March 4, 2004 at 10:06 am

    #107840

    is it possible to issue a delete statement

    without a transaction log being created. I would like this to work similar to the truncate command with no logging but i can't use the truncate because i want to delete only select rows ?

  • March 4, 2004 at 11:10 am

    #497359

    No.


    * Noel

  • March 4, 2004 at 11:15 am

    #497362

    I agree that No is the answer to your question, however I wanted to see if anyone could give me a clue as to why a

    Delete tablename

    works and a

    Truncate tablename

    does not.  It give me the error that a FK error exists, but gives no ref to the FK and I was unable to find one.  Using the Delete command gave me the desired results -- but with a great deal of extra logging -- table had ~ 3million records.

    Thanks for any help in understanding.

    -- NO

  • March 4, 2004 at 12:19 pm

    #497374

    Delete is a record by record logged opperation by DESING.

    TRUNCATE TABLE logs only the deallocation of whole data pages and that is why is fast. It was created for the special purpose of cleaning complete tables fast. there are no other ways using sql to delete data AFAIK.

    For massive deletes like in your case there are some methods:

    - bcp out data, Drop and recreate table and bcp in data

    -Chop the Deletes in small chunks (~2000 records) so that the transactions commit faster.

     HTH

     


    * Noel

  • March 4, 2004 at 12:34 pm

    #497375

    Thanks, noeld.  I appreciate the input and alternate suggestions.  I knew the difference in the two. I just don't understand why truncate would throw a FK error message and delete didn't.  On the size servers I am using speed wasn't the issue.  I just wanted to be careful about not filling up the log at the time of day it was.

     

  • March 4, 2004 at 1:35 pm

    #497390

    When you use truncate, ALL records are gone. Should you have a table with child records (meaning an fk from this one(Parent)) you get the error beause if the system let the delete operation happen those child records will be orphaned. That's why you should delete from child to parent

    On the other hand if you delete a record on the parent table and you have no associated child record no error is returned

    I hope is clear now

     


    * Noel

Viewing 6 posts - 1 through 5 (of 5 total)

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