Tuesday, January 31, 2012

Difference between Truncate and Delete in SQL

Here we will see the first practical example

Suppose we have following table named as tbl_Countries


Now we will use the delete operation on given table


if we want delete specific record in table we have to use the Delete operations
with where clause



































so as you have seen in above table you can delete specific row using Delete Operations

 if you want to delete all  records of the table just use the Truncate operation on your table.




so from the above example you can understand the clear difference between Truncate and Delete



Difference between delete and truncate


-Delete
 Can not be rolled back.
 DML command.
 Does not reset identity of table.
 It cannot activates trigger. mention transaction log of deleted rows.

- Truncate
Can be rolled back. DDL Command reset identity of table.
 Activates trigger.
 Does not mention transaction log.

No comments:

Post a Comment