Saturday, October 5, 2013

Difference Between Delete and Truncate Commands sql server

Delete Command Is a DML statement and  Maintain log for each of the record that will deleteDelete 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 notcreate 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 statement1.  Is a DDL Statement
2.  Will maintain log for  each of the record
2.  Will not create any log
3.  Little bit Slower3.  Faster than Delete
4.  Will fire the trigger, if any Delete trigger was defined on the table4.  Will not fire any trigger
5.  Contains the 'Where' clause5.  Does not contain 'Where' clause
6.  Maintains the Identity value6.  Reseeds the Identity value
7.  Can be Roll backed7.  Cannot be Roll backed
8.  Does not require Disabling of Referential Constraints8.  Requires Disabling the Referential Constraints

No comments:

Post a Comment

Opps Part 1 : Abstraction

  Abstraction in C# is a fundamental concept of object-oriented programming (OOP) that allows developers t...