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
WHEREclause 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;