Saturday, March 30, 2013

delete vs truncate vs drop



The DROP statement is distinct from the DELETE and TRUNCATE statements, in that DELETE and TRUNCATE do not remove the table itself.
For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database,
whereas a DROP statement would remove the entire table from the database.

truncate cmd doesnt del the structure (metadata),it only deletes the data
drop deletes every thing i.e data+structure(metadata)

delete requires commit and rollback as it is DML
and unlike delete truncate is DDL and thus gets implicit commit;

drop and truncate are DDL(implicit commit)
delete is DML (requires explicit commit or rollback)

No comments: