Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • Difference between truncate and delete

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 185
    Comment on it

    The following article captures the difference between truncate and delete.

    1) Rollback Possibility:

    It is possible rollback a DELETE operation but not a TRUNCATE operation.

    2) Impact on Identity

    TRUNCATE resets identity of a table but DELETE does not.

    3) Locking

    DELETE statement is executed using a row lock, each row in the table is locked for deletion.

    TRUNCATE always locks the table and page but not each row.

    4) Use of trigger

    DELETE activates a trigger because it is a row wise operation however triggers do not work with TRUNCATE.

    5) Use of WHERE clause

    We cannot use where condition in TRUNCATE however it is possible to use it with DELETE.

    6) Performance

    TRUNCATE is faster comapred to DELETE performance wise, because logs ae not maintained


    You cannot use TRUNCATE TABLE on tables satisfy below conditions:

    1) Are referenced by a FOREIGN KEY constraint.

    2) Are part of an indexed view.

    3) Are published by using transactional replication or merge replication.

    For tables with one or more of these characteristics, use the DELETE statement instead.

 0 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: