Thursday 11 October 2012

DIFFRENCE BETWEEN DELETE, TRUNCATE AND DROP





DELETE
TRUNCATE
DROP
Removes some or all rows from a table
Removes all rows from a table.
The DROP TABLE command removes one or more table(s) from the database.
A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
Does not require a WHERE clause, not allowed here.
All related data, indexes, triggers, constraints, and permission specifications for those tables are dropped by them operation.
It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
 Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
This is a DML command as it is just used to manipulate/modify the table data. It does not change the property of a table.
This is a DDL command as its resets identity columns, deallocates data pages and empty them for use of other objects in the database.

No comments:

Post a Comment