Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Difference between TRUNCATE, DELETE commands

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 596
    Comment on it

    TRUNCATE:

     

    •     Truncate command will remove all the rows from a table, there will be no WHERE clause in TRUNCATE command and there will be no data in the table after we run the truncate command.
    •     TRUNCATE is faster than DELETE command because when we run DELETE command, first all data is copied in one Tablespace and then data will delete, that's why when we rollback the command, we get back the data after deleting. But when run TRUNCATE command , it directly remove the data from the data without copying in Rollback Tablespace, that's why we cannot get the data back in case of TRUNCATE.
    •     It only data from a table, all other things are remain same like table structure,constraints, indexes and etc. The identity for new rows is reset.
    •     We cannot TRUNCATE TABLE, if it is refer to FOREIGN KEY constraint.
    •     No triggers will be fired while TRUNCATE table.  
    •     It is a DDL Command.    
    •     It cannot be rolled back.
    •     It resets identity of the table
    •     Less information is logged in case of TRUNCATE command as compared to DELETE command.In DELETE command , information is logged for each entry but it will not happen in TRUNCATE command.

    DELETE:

     

    •     Delete command removes all the rows from a table based on certain condition that we provide with a WHERE clause. If we are not providing WHERE clause then all rows from a table will be removed.
    •     It removes rows one at a time and records an entry in the transaction log for each deleted row.
    •     It is DML Command.Therefore, DELETE command will be rolled back.
    •     This operation will cause all DELETE triggers on table to fire.
    •     It can be rolled back.
    •     It does not reset the identity of the table. The identity of a table remains same.

 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: