Sunday, June 13, 2010

Difference Between Delete and Truncate Commands

Delete Command Is a DML statement and  Maintain log for each of the record that will delete. Delete fires the trigger which was defined on it.Delete maintains the Identity value.Delete can be Roll backed

Truncate Command Is a DDL Statement and does not create any log while truncating. That's why Truncate is faster than Delete. Truncate will not fire any trigger. Truncate Reseeds the Identity value. Once Truncate Operation has been done, we can not Roll back the changes. 

Now let us see all the differences between these two commands 

DELETE

TRUNCATE

1.  Is a DML statement 1.  Is a DDL Statement

2.  Will maintain log for  each of the record

2.  Will not create any log
3.  Little bit Slower 3.  Faster than Delete
4.  Will fire the trigger, if any Delete trigger was defined on the table 4.  Will not fire any trigger
5.  Contains the 'Where' clause 5.  Does not contain 'Where' clause
6.  Maintains the Identity value 6.  Reseeds the Identity value
7.  Can be Roll backed 7.  Cannot be Roll backed
8.  Does not require Disabling of Referential Constraints 8.  Requires Disabling the Referential Constraints

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.