Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Unanswered: Delete vs. Truncate

    If I am deleting all the rows in a table why would I use Truncate Table over Delete, or vice vera? What is the difference?

    Ken

  2. #2
    Join Date
    Jul 2002
    Posts
    63
    The Truncate is much faster.
    The reason truncate does not record in the transaction log therefore you can not role it back

    Eyal

  3. #3
    Join Date
    Jan 2003
    Posts
    126
    Okay,

    That makes sense. I ran across it in a DTS package that someone else created, that basically adds new data to a table.

    It creates temp tables, and one of them they were doing a delete on, which didn't quite make sense to me.

    Thanks for the reply!

    Ken

  4. #4
    Join Date
    Mar 2003
    Posts
    6
    You cannot Truncate from a table used in replication (because it is not logged).

  5. #5
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Originally posted by eschapir
    The Truncate is much faster.
    The reason truncate does not record in the transaction log therefore you can not role it back


    I am not sure if you guys have experienced this. Truncate command may not work at times. And this is not just happening in my current job but previous job too. When truncate command was used, old records were not removed and when new set of data was loaded, it caused duplicates in the table.

    So for peace of mind, I usually placed both truncate and delete commands (though delete will do the job) in some of my missing critical processes.

    Sound really silly but it actually happened to me.

    I am on SQL7 SP4.




    Eyal

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    TRUNCATE TABLE is aimed at administrative tasks.(hard come, light gone)

    1. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

    2. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

    3. TRUNCATE TABLE may not be used on tables participating in an indexed view.

    4. Cannot be used on linked server.

    5. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

    For more see BOL Topic "TRUNCATE TABLE" and other sources

    Good luck !

  7. #7
    Join Date
    Jan 2003
    Posts
    126
    Thanks guys!

    Now I know when to use each one. For this particular task I can use truncate. Actually I could just drop the table and recreate it. Delete take a lot longer to run for this particular process.

    Doesn't really cause any issues either way, but when you don't have much time for jobs at night, speed and time saved is always a consideration!

    Thanks again! Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •