Un tester desidera cancellare una tabella di staging prima di una nuova esecuzione del test. Dovrebbe essere DELETE, TRUNCATE o DROP? La scelta sbagliata può significare un lavoro lento, un tavolo mancante o, in produzione, un pomeriggio molto brutto.

Abbiamo eseguito gli esempi DELETE e DROP di seguito in SQLite 3.50 e gli output provengono da quell’esecuzione. SQLite non ha un comando TRUNCATE, quindi le note TRUNCATE si basano sulla documentazione ufficiale di PostgreSQL, MySQL e SQL Server piuttosto che sul nostro test.

La risposta breve

• Hai bisogno di rimuovere alcune righe? Utilizzare DELETE ... WHERE.
• Hai bisogno di svuotare un tavolo ma mantenerlo? Utilizzare TRUNCATE, dove è supportato.
• Hai bisogno che il tavolo sparisca per sempre? Utilizzare DROP TABLE.

ELIMINA: rimuove le righe

DELETE è un comando di modifica dei dati (DML). Rimuove le righe una alla volta, attiva trigger a livello di riga e può essere filtrato. Impostiamo una piccola tabella con un trigger di controllo:

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

È andata solo la riga corrispondente e il trigger l’ha registrata. L’esecuzione di DELETE FROM logs senza una clausola WHERE ha rimosso le altre tre righe e la tabella di controllo conteneva quattro voci, una per riga eliminata.

DELETE non reimposta i contatori con incremento automatico. Dopo aver svuotato la tabella, il nostro successivo inserimento ha ricevuto id 5, non 1. In SQLite ciò è dovuto a AUTOINCREMENT. Anche le colonne di identità di MySQL InnoDB e SQL Server continuano da dove si trovavano dopo un DELETE.

TRONCARE: svuota la tabella

TRUNCATE TABLE logs; rimuove ogni riga contemporaneamente. Non può accettare una clausola WHERE. Invece di eliminare le righe una per una, il database in genere rilascia le pagine di dati della tabella, motivo per cui è molto più veloce su tabelle di grandi dimensioni.

Quando lo abbiamo eseguito in SQLite, abbiamo ricevuto un errore di sintassi, perché SQLite non ha il comando:

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

In SQLite, utilizzare DELETE FROM logs; senza clausola WHERE. La documentazione di SQLite descrive una “ottimizzazione del troncamento” che lo rende veloce quando la tabella non ha trigger. La nostra tabella aveva un trigger, quindi l’eliminazione veniva eseguita riga per riga e veniva attivata per ogni riga.

Nei database che supportano TRUNCATE, tramite la loro documentazione:

• Normalmente non attiva i trigger DELETE a livello di riga.
• Di solito reimposta i contatori di identità o di incremento automatico, ad esempio in MySQL e SQL Server. In PostgreSQL aggiungi RESTART IDENTITY.
• Richiede permessi più forti rispetto a DELETE nella maggior parte dei sistemi.

DROP: rimuove la tabella stessa

DROP TABLE è un comando schema (DDL). Rimuove i dati, la definizione della tabella, i relativi indici, trigger e vincoli. Anche tutto ciò che fa riferimento alla tabella, come visualizzazioni o chiavi esterne, viene influenzato.

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS è utile negli script di installazione che possono essere eseguiti più di una volta. Le viste su una tabella eliminata possono interrompersi, come abbiamo mostrato in Spiegazione delle viste SQL.

Tabella comparativa

Puoi tornare indietro?

È qui che molti articoli semplificano eccessivamente. La risposta onesta è che dipende dal database.

DELETE può essere ripristinato ovunque all’interno di una transazione. Lo abbiamo testato:

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

È stato eseguito anche il rollback di DROP in SQLite, che supporta DDL transazionale:

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

Per TRUNCATE e DROP in altri database, secondo la loro documentazione:

Quindi “TRUNCATE non può mai essere annullato” è sbagliato per PostgreSQL e SQL Server, e “può sempre essere annullato” è sbagliato per MySQL. Al di fuori di una transazione, nessuna di esse può essere annullata senza un backup.

Perché TRUNCATE è veloce

DELETE registra ogni riga rimossa in modo che la modifica possa essere annullata e replicata, attiva i trigger e controlla i vincoli riga per riga. Su una tabella con milioni di righe, ciò crea molto lavoro e volume di log. TRUNCATE generalmente dealloca lo spazio di archiviazione in un’unica operazione, registrando molto meno. Per rimuovere un sottoinsieme di grandi dimensioni anziché tutto, elimina in batch in modo da evitare blocchi lunghi. Copriremo i costi di blocco e query nell’ottimizzazione delle query SQL.

Chiavi esterne e permessi

• Chiavi esterne. SQL Server rifiuta di TRONCARE una tabella a cui fa riferimento una chiave esterna. PostgreSQL richiede di troncare anche le tabelle di riferimento o di utilizzare CASCADE. Un DELETE controlla invece ogni riga rispetto ai vincoli.
• Autorizzazioni. DELETE necessita dei diritti di eliminazione. TRUNCATE spesso necessita di ALTER o di un diritto TRUNCATE dedicato e DROP necessita della proprietà o di ALTER sullo schema.
• Replica e controllo. Se altri sistemi dipendono da log di modifiche o trigger a livello di riga, TRUNCATE potrebbe ignorarli. Verificare prima con chi possiede quei sistemi.

Quale usare

Per i dati dei test, TRUNCATE è popolare perché reimposta gli ID, rendendo i test ripetibili. Avvolgere ciascun test in una transazione di cui viene eseguito il rollback è un’altra opzione, spiegata in Transazioni SQL e ACID.

Lista di controllo di sicurezza

1. Conferma a quale database e ambiente sei connesso.
2. Eseguire prima la clausola WHERE come SELECT.
3. Avvolgi la modifica in una transazione in cui il tuo database la supporta.
4. Avere un backup recente e testato prima di TRUNCATE o DROP in produzione.
5. Inserisci le istruzioni DROP negli script di migrazione esaminati, mai in console ad hoc.
6. Controlla cosa dipende da una tabella, come visualizzazioni, chiavi esterne e report, prima di rilasciarla.

Eliminazione sicura di grandi quantità di dati

La rimozione di milioni di righe con un DELETE può bloccare la tabella per un lungo periodo, riempire il registro delle transazioni e rallentare la replica. Uno schema comune è quello di eliminare in piccoli lotti e ripetere finché non rimane più nulla:

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

Esegui l’istruzione in un ciclo, con una breve pausa tra i batch, finché non riporta zero righe. Ogni batch è una piccola transazione, quindi i blocchi sono brevi e le altre query continuano a essere eseguite. Un indice sulla colonna del filtro, qui created_at, mantiene veloce ogni batch.

Per i dati che crescono in base alla data, come registri ed eventi, il partizionamento delle tabelle è ancora migliore. Ogni mese vive nella propria partizione e la rimozione dei vecchi dati diventa un’operazione rapida che elimina o tronca una partizione invece di eliminare le righe una per una.

Eliminazioni soft

Molte applicazioni non eliminano mai fisicamente i record aziendali. Aggiungono una colonna come deleted_at e la impostano invece, quindi le query filtrano su deleted_at IS NULL. Le eliminazioni temporanee semplificano il ripristino e conservano la cronologia per i controlli, ma ogni query deve ricordare il filtro, il che è un buon lavoro per una visualizzazione. Anche i vincoli univoci necessitano di attenzione, perché una riga eliminata temporaneamente mantiene il suo valore. Le leggi sulla privacy possono richiedere l’effettiva eliminazione dei dati personali dopo un certo periodo, pertanto le eliminazioni soft non sostituiscono una politica di conservazione.

Considerazioni finali

DELETE è lo strumento preciso e attento. TRUNCATE è il pulsante di ripristino rapido e DROP rimuove completamente la tabella. Il rischio maggiore è presupporre che un database si comporti come un altro, in particolare in merito al rollback. In caso di dubbi, testare prima il comando su una copia della tabella nel proprio database.

Domande frequenti

Continua a imparare