SQL Delete vs Truncate vs Drop

By: Chris Dunn

There are three common commands used to remove data from a SQL database.  The commands are DELETE, TRUNCATE and DROP.  I've seen DELETE and TRUNCATE used more interchangeably than they should and threw in DROP to round out the commands.  Each command serves a different use case and so it deserves a bit of attention to understand how they are different.

DELETE

Delete is a row level command and referred to as DML (Data Manipulation Language).  It allows you to remove rows from a table with the ability to apply a WHERE clause to specify only certain rows to delete.  It applies a row level lock as it removes each row.  It impacts the individual rows themselves and doesn't change the underlying table.  Because of this the identity column is left intact and is not reset.  You are able to rollback this command, which is not the case with all commands.

DELETE FROM Customers WHERE LastName='Dunn';

TRUNCATE

Truncate removes all rows from a table and you are not able to specify a WHERE clause.  This command is executed against the table so it utilizes a table level lock and is called a DDL ( Data Definition Language) command, which modifies database structure.  Any identity column will be reset with this command.  This command cannot be rolled back.

TRUNCATE TABLE Customers

DROP

Drop is also a DDL command, but in this command it removes the entire table from the database, rows and all.  Like the Truncate command, this command cannot be rolled back.

DROP TABLE Customers

Hopefully that helps clarify the common SQL commands to remove data and tables.  Play with the commands in a test database with SELECT statements before and after so you can see the changes and how the command interacts with the table, rows and identity columns.