Difference between Delete and Truncate
Submitted by Kuldeep on Sat, 02/19/2011 - 00:22
Removing records from a table is a crucial operation. So I would like to pen down my knowledge about removing records from a table. There are two ways to do the same, Truncate and Delete.
I would like to explain the basic difference between two.
1. Delete is a DML whereas truncate is a DDL. Truncate deallocates the data pages for the table and does'nt delete them row by row. So if there is an Identity column in the table it would be reset to initial seed after truncate.
2. For Delete a Transaction Log is maintained. For this simple reason a Delete statement can be RolledBack and recovered using the transaction log.
On the other hand, for Truncate there is no entry in transaction log. So a truncate statement can't be recovered once committed. But we can always rollback a truncate statement if we trap it inside a TRANSACTION scope.
3. Where Clause can’t be used in truncate, so a truncate statement can't LOCK Rows.
Syntax of both operations is as below
DELETE * FROM TableName WHERE ClauseTRUNCATE TableName 4. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
Please go through this URL
Please go through this URL for more points
Nice explanation.
Really awesome post and you have explained everything very clearly about Delete and truncate.
Reply to comment | TechPint
If some one ωants to be updated wіth latеst technοlogіes then
he must be visit thiѕ web page and be up to date every day.
Post new comment