• 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.
Wednesday, February 17, 2010
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment