Risposta

DELETE e TRUNCATE sono entrambi strumenti per pulire le tabelle, ma funzionano in modo diverso:

• DELETE elimina righe in base a una condizione o tutte, supporta WHERE, può essere annullato, attiva i trigger. Potrebbe essere lento con un gran numero di righe (una alla volta).
• TRUNCATE pulisce immediatamente l'intera tabella, non può usare WHERE, di solito non è registrato riga per riga, non attiva sempre i trigger. Nella maggior parte dei casi, l'azione non può essere annullata e non può essere ripristinata in alcuni DBMS.

Dettagli:

• DELETE supporta il rollback della transazione (ROLLBACK), TRUNCATE non lo fa o dipende dal DBMS.
• TRUNCATE di solito azzera il contatore identity/sequence.
• TRUNCATE non è applicabile se ci sono chiavi esterne o riferimenti alla tabella.

Esempio:

-- Cancellare ordini più vecchi di 3 anni
DELETE FROM orders WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

-- Pulire completamente la tabella (velocemente)
TRUNCATE TABLE logs;

Domanda insidiosa

È possibile ripristinare i dati dopo TRUNCATE?

Errore comune: si pensa che, come DELETE, TRUNCATE possa essere annullato da una transazione. Ma TRUNCATE di solito non può essere annullato o eseguito UNDO, a meno che non si usino alcuni DBMS (ad esempio, PostgreSQL all'interno di una transazione).

Esempio (nella maggior parte dei DBMS):

BEGIN;
TRUNCATE TABLE orders;
ROLLBACK; -- Nella maggior parte dei DBMS non funzionerà, dati persi!

Esempi di errori reali a causa dell'ignoranza delle sottigliezze del tema

---

Storia

Cancellazione di dati temporanei in un DB di produzione con TRUNCATE invece di TEST. Il trigger di audit per la cancellazione delle righe è scattato solo su DELETE — le informazioni sui dati persi, sul tempo e sull'utente sono assenti. Ha danneggiato l'analisi dell'incidente.

---

Storia

Pulizia della tabella con TRUNCATE in presenza di chiave esterna. SQL ha lanciato un errore a causa del vincolo di integrità referenziale, lo script non è stato completato, l'automazione si è arrestata. Soluzione: rimuovere temporaneamente le chiavi esterne o utilizzare DELETE.

---

Storia

Cancellazione di massa di dati attraverso DELETE in tabelle grandi senza batching: a causa dei lunghi blocchi, il database "si è bloccato", altre transazioni attendevano il rilascio dei blocchi. Risultato — fermo del sistema. Corretta: utilizzare DELETE LIMIT/OFFSET, se supportato, o batching.