DEV Community

Pavol Z. Kutaj
Pavol Z. Kutaj

Posted on

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
Enter fullscreen mode Exit fullscreen mode

TRUNCATE TABLE

TRUNCATE TABLE 'foo.bar'
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

LINKS

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

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

đź‘‹ Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay