Explaining DELETE vs DROP vs TRUNCATE in SQL

The aim of this page📝 is to explain a difference between three similar clauses in SQL: DELETE FROM vs TRUNCATE TABLE vs DROP TABLE. A simple one, but essential.

DELETE FROM

• You should be able to undo this
• Deletes one row at a time
• Logs each row in the transaction log
• LSN (log sequence number) is kept, too
• Supports WHERE clause for selective row deletion

DELETE FROM <table>
DELETE FROM books -- all has been dropped
DELETE FROM books WHERE author = 'Smith' -- only Smith's books deleted

TRUNCATE TABLE

• faster
• deletes all records in a table by deallocating the data pages used by the table

TRUNCATE TABLE 'foo.bar'

DROP TABLE

• Removes the entire table structure and all data
• Cannot be rolled back (most DBMS)
• Removes table definition from database schema
• Frees all space associated with the table

DROP TABLE tablename;
DROP TABLE IF EXISTS tablename;

LINKS

https://www.mssqltips.com/sqlservertip/1080/deleting-data-in-sql-server-with-truncate-vs-delete-commands/