Difference between TRUNCATE, DELETE and DROP?
DELETE and TRUNCATE are two SQL 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 remove some rows. If no WHERE condition is specified, all rows will be removed.
– Causes all DELETE triggers on the table to fire.
– It de-allocates 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 de-allocated instantly and remain allocated in the heap. Thus, no other object can reuse this associated space. Thus to de-allocate 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 it is just used to manipulate/modify the table data. It does not change any 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 de-allocates 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 it resets IDENTITY columns, de-allocates 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 roll-backed. But in reality both DELETE and TRUNCATE operations can be COMMITTED AND ROLL-BACKED 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 the Table are dropped by this 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 de-allocation and locked until the transaction commits. In the physical phase, the IAM pages marked for de-allocation are physically dropped in batches.