DEV Community

Pavol Z. Kutaj
Pavol Z. Kutaj

Posted on

1 1

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/

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

đź‘‹ Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay