SQL: DELETE, DROP, TRUNCATE

I was kind of surprised when I was hit with this question from a junior coworker of mine a couple of days ago: "What is the difference between the SQL DELETE, DROP, and TRUNCATE commands?".

Besides forming the acronym of my favorite professional wrestling move, DDT, there is quite a difference between the three commands.

DELETE
The DELETE command is a logged row deletion operation. What does this mean to the average Joe? In database terminology, a logged operation is any operation that can be executed in a transaction and then either committed to storage or rolled back in case of errors.

The DELETE command is a logged operation, in that every row that is deleted is logged to the transaction log, which effectively makes this command quite slow when deleting a large number of rows since every row is referenced. Even though it is slow, it is ideal for transactions: if anything goes wrong, the deletion can easily be rolled back.

A DELETE command is a DML (Data Manipulation Language) command that it why it is a logged operation. An optional WHERE clause can be used to specify a criteria rows much meet in order to be deleted. Because it is a DML command, any database triggers will also be fired when a DELETE command is executed.

Using the DELETE command does not reset the identify column to its original seed. So any new rows inserted after the DELETE command will take not have their value for the identify column restart from the seed. The identify column is also sometimes refereed to as an auto increment column in some databases, such as MySQL.

For example, let's assume I have the following table:

EmployeeId | Name
--------------------
1 | Ahmed
2 | Mostafa

After a DELETE command, if I were to insert a new row, the row will look like this; notice the EmployeeId column:

EmployeeId | Name
--------------------
3 | Amr

DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. It is ideal when an entire table, along with its rows, should be removed.

The DROP command is a DDL (Data Definition Language) command, hence it is not a logged operation and cannot be rolled back. Once it executes, its done so you should know what your doing otherwise you risk losing valuable data. Because it is a DDL command, no database triggers will be fired when a DROP command executes.

I don't think I need to point this out, but you cannot insert new rows after a DROP command because the table no longer exists! You would need to recreate the table before you insert new rows.

TRUNCATE
The TRUNCATE command removes all rows from a table. Unlike the DELETE command, it is not a logged operation and thus cannot be rolled back. Because it is not logged, it is faster than the DELETE command in emptying out an entire table.

Instead of referencing each row one by one like the DELETE command, the TRUNCATE command simply de-allocates the data pages occupied by the table. So it references the first data page and the last data page and simply deletes everything in between in one pass.

The TRUNCATE command is a DDL (Data Definition Language) command, so it cannot be rolled back. Unlike the DELETE command, a WHERE clause cannot be used; you either empty out the entire table or you don't. Also because it is a DDL command, no database triggers will be fired when a TRUNCATE command executes!

Unlike the DELETE command, the TRUNCATE command does reset the identify column to its original seed. Unlike the DROP command, the table structure remains the same, along with any constraints, indexes, etc.

Here is a major downside to the TRUNCATE command however; On most databases, if not all, it cannot be used when there is a Foreign Key constraint. This is to protect the integrity of the data. To workaround this problem, either a DELETE command should be used instead, or the constraint has to be dropped.