Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What does the Truncate do differently to drop?

I believe it deletes all the data in the table but keeps the table name in the database, where as DROP deletes all data and the table. Is this correct ?

share|improve this question

migrated from stackoverflow.com Mar 19 at 15:08

This question came from our site for professional and enthusiast programmers.

    
Related (and probably a lot of the concepts will be similar or identical, even if not all of the fine details are): dba.stackexchange.com/questions/30325/delete-vs-truncate –  Aaron Bertrand Mar 19 at 17:14
add comment

4 Answers 4

DELETE - DATA MANIPULATION LANGUAGE(DML)

The DELETE statement in any RDBMS is considered a DML statement. Also known as CRUD (Create, Read, Update, Delete), this kind of statement is intended to manipulate data in a database without affecting the underlying structure of the objects. What this means in practical term is:

  • A DELETE statement can be fine-tuned using a predicate via either WHERE or JOIN to delete some or all rows in a table.
  • A DELETE statement will be logged by the database and can be rolled back within a transaction if the statement fails.
  • Typically a DELETE will take row level locks on the data it is deleted, though this could escalate higher as necessary.
  • Because of the transactional overhead, DELETE can be "slow" (this is relative), but safer because it is fine-grained.

TRUNCATE - DATA DEFINITION LANGUAGE(DDL)

TRUCNATE is considered a DDL statement, meaning that it is intended to alter how objects are defined in a database. Usually, DDL statements are CREATE, ALTER, or DROP, but TRUNCATE serves a particular purpose, that of "resettting" a table by removing all the rows. The methods of this differ between the RDBMS engines and I would recommend looking at the specifics of MySQL. The practical implications of a TRUNCATE are:

  • TRUNCATE can not be fine grained. If successful, it will remove all the rows from your table.
  • TRUNCATE is typically not logged. This varies by RDBMS and I would suggest you looks more specifically at how MySQL handles it. (Hint, it varies by version.)
  • TRUNCATE requires a table metadata lock to execute. How this is actually implemented can be specific to RDBMS, but essentially the TRUNCATE process must prevent other processes from mucking with the table in order to execute its DDL.
  • Because it is (typically) not logged and does not use predicates, a TRUNCATE will be faster than a DELETE, but less safe.

DROP TABLE - DATA DEFINITION LANGUAGE(DDL)

DROP TABLE goes further than a TRUNCATE in that it actually removes the table from the database completely. This includes removing all associated objects, such as indexes and constraints. If you dropped a table, not only would you remove all the data, but the structure as well. This will usually be done when a table is no longer needed. The primary concern is that since a DROP is DDL, you typically can't undo it. Some RDBMS engines will allow you to wrap DDL within a transaction so you can roll it back, but this is not considered best practice and should be avoided.

share|improve this answer
2  
Nice expanded explanation of DELETE and TRUNCATE. +1 for going the extra mile. –  RolandoMySQLDBA Mar 19 at 17:18
    
I would add that TRUNCATE resets the table's auto-increment counter. –  DanMan May 24 at 10:19
add comment

Although I cannot speak for MySQL, here's a quick table comparing some aspects of truncate vs. delete within Oracle.

truncate                             |   delete
---------------------------------    |   ---------------------------
DDL   (implicitly commits,           |   DML
    including any pending DML)       |  
Does not generate undo info          |   Generates undo info
    (rollback statements)            |       (rollback statements)
Resets high water mark in table      |   Does not affect full-scan 
    and all indexes, improving       |       performance
    full-scan performance            |      
Does not fire any delete triggers    |   Fires delete triggers
Priv to truncate cannot be granted   |   --
    to truncate another user's       |  
    table; DROP ANY TABLE system     |   
    priv required                    |      
Storage for table and indexes can    |   Never shrinks the size of a 
    be set to initial size           |       table or indexes
Cannot truncate parent table from    |   --
    an established referential       |  
    integrity constraint; must       |  
    first disable foreign key        |  
    that references the parent       |  
    table                            |  

Information compiled from this book: http://amzn.com/0470395125

Hopefully this may help any Oracle users that stumble across this page, as I did. Please feel free to point out which, if any, of these points hold true in MySQL,

share|improve this answer
    
+1 for mentioning triggers. –  DanMan May 24 at 10:21
add comment

DROP TABLE TableName -> delete the table from the database.

TRUNCATE TABLE TableName -> delete the table's data, unconditionally... and reset the IDENTITY seed value, if the table has an IDENTITY field.

share|improve this answer
add comment

Delete:

  1. The mysql command 'DELETE' is used to delete space allocated by mysql server and the structure of table remains same.
  2. It is removed rows.
  3. It can be used by either 'WHERE' clause or without it in mysql.
  4. The data can be roll backed, if it is removed by 'DELETE' mysql command. If the transaction is used which is yet committed before delete the row, then you can roll backed again.

Drop:

1.Remove whole table from mysql database.
2. The data cannot be roll backed if it is dropped by 'DROP' mysql command.

Truncate:

1. Remove rows from mysql table but the structure of table remains same.
2. The data cannot be roll backed if it is deleted by 'TRUNCATE' mysql command.
3. It can be used by only without 'WHERE' clause in mysql.

Reference: visit

share|improve this answer
    
This doesn't add anything new - all you noted has already been said in previous, more complete, answers. –  Mat May 24 at 7:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.