Difference between DROP and TRUNCATE

Question

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 ?

Answer 1

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.

Answer 2

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,

Answer 3

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.

Answer 4

• DELETE: DML command, if you perform delete command remove the only DATA without disrobing the table Structure we can ROLLBACK the data row by row manner

• TRUNCATE: DDL command, if you perform Truncate command remove the only DATA without disrobing the table Structure we can't rollback the data truncate remove the data in a PAGE BY PAGE manner truncate is faster then delete query

• DROP:, DDL command, if you perform the DROP command its delete(remove) the data and total table structure also we can't rollback the data