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/