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 ?
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 only takes a minute to sign up.Sign up to join this community
DELETE - DATA MANIPULATION LANGUAGE(DML)
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:
DELETEstatement can be fine-tuned using a predicate via either
JOINto delete some or all rows in a table.
DELETEstatement will be logged by the database and can be rolled back within a transaction if the statement fails.
DELETEwill take row level locks on the data it is deleted, though this could escalate higher as necessary.
DELETEcan 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
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
TRUNCATEcan not be fine grained. If successful, it will remove all the rows from your table.
TRUNCATEis 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.)
TRUNCATErequires a table metadata lock to execute. How this is actually implemented can be specific to RDBMS, but essentially the
TRUNCATEprocess must prevent other processes from mucking with the table in order to execute its DDL.
TRUNCATEwill 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.
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,
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