Skip Navigation LinksHome > Categories > Code from a Category

What is difference between delete and truncate



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 2018
Add Date: 01/01/2012
Here, we will discuss difference between delete and Truncate
TRUNCATE

1. It is DDL command
2. Speed is faster
Reason: When you type DELETE. all the data get copied into the Rollback Tablespace first. then delete operation get performed. Thats why when you type ROLLBACK after deleting a table you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time. But when you type TRUNCATE it removes data directly without copying it into the Rollback Tablespace. That's why TRUNCATE is faster. Once you truncate you can't get back the data.
3. Do not Check Constraints.
4. Roll back is not possible.
5. Cannot use with where clause
6. When a table is truncated the memory occupied is released
7. The truncate statement will result in clearing table spaces or memories and the table structure remain in the database. Therefore it free table storage spaces and use it only when you need to remove all data from a table.
8. Removes the data by deallocating the data pages used to store the table's data and only the page deallocations are recorded in the transaction log.
9. Removes all rows from a table but the table structure and its columns constraints indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
10. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint;
11. Because TRUNCATE TABLE is not logged it cannot activate a trigger


DELETE

1. It is a DML command
2. Speed is slow
3. Check constraints, If Exists then show error.
4. We can rollback.
5. User Where Clause
6. is slower than truncate as it is dml has to go through rollback segments
7. We can use where clause with delete. When a table is deleted memory occupied is not released and also the water mark is not adjusted.
8. The delete statement will result in the table spaces or memories would remain as the current size and table structure remain in the database.
9. It is slower than the truncate command.
10. Delete, use check constraints if exits then show error.
11. Removes rows one at a time and records an entry in the transaction log for each deleted row.
12. If you want to retain the identity counter use DELETE instead. If you want to remove table definition and its data use the DROP TABLE statement.
13. Activates Trigger

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.