.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

What is difference between delete and truncate

Posted By:Vishal Nayan       Posted Date: September 01, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

In this article we will see What is difference between delete and truncate

What is difference between delete and 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


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

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend