Difference between TRUNCATE, DELETE and DROP?

DELETE & TRUNCATE are two TSQL commands used to remove records from a particular table. But they differ in how they execute and operate.

DELETE: (http://msdn.microsoft.com/en-us/library/ms189835.aspx)
- Removes some or all rows from a table.
- A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
- Causes all DELETE triggers on the table to fire.
- It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
- According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
- Thus it requires more locks and database resources.
- This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.

TRUNCATE: (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
- Removes all rows from a table.
- Does not require a WHERE clause, not allowed here.
- Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
- No triggers are fired on this operation because it does not log individual rows.
- It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
- Thus it also requires less number of locks.
- TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
- This is a DDL command as its resets identity columns, deallocates data pages and empty them for use of other objects in the database.

Note: It is a misconception among some people that TRUNCATE cannot be rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE & TRUNCATE both can be rollbacked if provided inside a transaction. The only method to rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.

DROP: (http://msdn.microsoft.com/en-us/library/ms173790.aspx)
The DROP TABLE command removes one or more table(s) from the database.
- All related data, indexes, triggers, constraints, and permission specifications for those tables are dropped by them operation.
- Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
- Cannot drop a table that is referenced by any Foreign Key constraint.
- According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

About these ads