Friday, April 8, 2011

Difference between truncate and Delete

Difference between truncate and Delete
Truncate
Delete
Deletes all the records by deallocating the pages which reduces the resource overhead of logging the deletion and no. of lock acquired.

Only one record is entered in the transaction log that is the deallocation of page.
Deletes one row at a time which is entered in the transaction log as LSN(Log sequence no.).
Records removed by the truncate command cannot be restored.
since the transaction log is maintained it can be roll backed or restored.
You cannot specify the where clause in the truncate command.
We can delete specific records.
Truncate cannot be used delete the tables which contains the reference of foreign keys since it doesnot fire event triggers (delete / update trigger).

Truncate resets the identity back to the seed


When deleting a large data the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).
DBCC SHRINKDATABASE (db_name)

When truncate cannot be used then the alternate using delete command is
DELETE from "table_name"
DBCC CHECKIDENT("table_name", RESEED, "reseed_value")
Truncate cannot be used in replication or log shipping since it may need transaction log files for maintanence

No comments:

Post a Comment