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
    • 171
    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


    Restrictions

    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
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
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: