Wednesday, February 17, 2010

Difference between Delete and Truncate?

•    Delete table is a logged operation, so the deleting of each row gets logged in the transaction log, which make it slow.
•    Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.
•    Truncate table is functionally identical to delete statement with no “where clause”. Truncate table is faster and uses fewer system and transaction log resources than delete.
•    We cannot use truncate tale on table referenced by a foreign key constraints, instead, use delete statement without a where clause. Because truncate table is not logged, it cannot active a trigger.
•    Truncate table nay not be used in tables participating in an indexed view.
•    Truncate is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
•    Delete is a DML command and can be rolled back.

No comments:

Post a Comment