Why Truncate is faster than Delete?

Sponsored Links:

DELETE is a logged operation on a per row. This means that the deletion of each row is recorded & removed physically. You can delete any row that does not involve a violation of restriction, while leaving the foreign key or any other restrictions in place.
TRUNCATE is a logged operation, but in a different way. TRUNCATE logs the deal location of information pages in which information exist. The deallocation of information pages means that your information rows still actually exist in the information pages, but the extensions are marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE. Can not truncate a table that has foreign key constraints. You must remove the constraints, truncate the table, & reapply the constraints.

TRUNCATE will reset any identity columns to default value initialization. This means that if you have a table with an identity column & has 264 rows with a seed value of 1, his latest record will have the value 264 (assuming you started with value 1) in the columns of your identity. After truncating your table when you insert a new record in to the empty table, the identity column will have a value of 1. DELETE won't. In the same scenario, if the current row, inserting a new row in to the empty table, the identity column has a value of 265.

Why Truncate is faster than Delete?
Reason: In writing to delete all information is copied to the rollback table space & then delete operation is performed. So when you type ROLLBACK after deleting a table, you can get back the information (the method get it for the pace of Rollback Tables). This whole technique will take time, but when using TRUNCATE, it removes information directly without copying it to Rollback table space. So TRUNCATE is faster. Two times truncated you can not get back the information.

No comments: