Delete e Truncate sono due istruzioni SQL che spesso generano confusione tra gli sviluppatori e i data analyst. Ad una prima analisi, la differenza principale comune a tutti i database è che la Truncate elimina tutti i record di una tabella, mentre con la Delete si ha la possibilità di cancellare solo le righe che rispettano uno specifico filtro inserendo una clausola where. In questo articolo andremo ad analizzare altre importanti differenze tra Delete e Truncate che uno sviluppatore SQL devo assolutamente conoscere.
Effetto di Delete e Truncate su una colonna Identity
Molte tabelle hanno una colonna contenente un progressivo popolato automaticamente dal database ad ogni inserimento, che funge solitamente da chiave surrogata. Su Sql Server possiamo creare una colonna del genere con questo codice:
CREATE TABLE TabellaConIdentity(
Colonna1 INT NOT NULL
PRIMARY KEY IDENTITY(1,1),
Colonna2 VARCHAR(50)); Come accennato in precedenza, il valore della colonna di tipo Identity verrà popolato direttamente dal database ogni volta che inseriamo una nuova riga.
Abbiamo già detto nel paragrafo introduttivo che con la delete si ha la possibilità di scegliere quali righe eliminare. Sarebbe però un errore credere che le due query DELETE FROM TabellaConIdentity e TRUNCATE TABLE TabellaConIdentity siano equivalenti su tutti i DBMS.
Ad esempio, su Sql Server e MySql la Truncate ha l’effetto aggiuntivo di eseguire il reset della colonna Identity. Dopo una Truncate i nuovi valori di una colonna Identity ricominceranno sempre dal valore iniziale (solitamente 1), mentre con la Delete si proseguirà considerando i valori già inseriti prima della rimozione delle righe.
Vediamo questo esempio su un database Sql Server:
- inseriamo una riga nella tabella tramite l’istruzione INSERT INTO TabellaConIdentity(Colonna2) VALUES (‘Test’);
- otterremo in automatico il valore 1 nella prima colonna di tipo Identity;
Colonna1 Colonna2 1 Test - eliminiamo la riga con l’istruzione DELETE FROM TabellaConIdentity: la tabella sarà vuota;
- inseriamo una nuova riga con lo stesso codice del punto 1, otterremo nella prima colonna il valore 2;
Colonna1 Colonna2 2 Test - eliminiamo ora i dati con il codice TRUNCATE TABLE TabellaConIdentity. Anche in questo caso la tabella sarà vuota;
- inseriamo di nuovo una nuova riga con lo stesso codice del punto 1, questa volta il valore dell’Identity viene “resettato”.
Colonna1 Colonna2 1 Test
Su Oracle una colonna creata con la clausola GENERATED AS IDENTITY non è soggetta a reset né con la Delete, né con la Truncate.
Effetti di Delete e Truncate su una transazione
Sul database Oracle, lanciare una Truncate ha l’effetto di eseguire un commit implicito di tutte le transazioni attive nella sezione corrente. L’operazione di Delete invece non ha questo effetto, di conseguenza è più adeguata a gestire le operazioni di cancellazione (anche massiva) dei dati quando c’è la necessità di eseguirle in transazione.
Su MySql la situazione è molto simile a Oracle. Su SQL Server invece la Truncate non comporta il commit di una transazione aperta esplicitamente in precedenza.
Ricordiamo sempre che in generale, secondo le impostazioni di default, su MySql e Sql Server occorre aprire esplicitamente una transazione tramite, rispettivamente, START TRANSACTION e BEGIN TRANSACTION. Su Oracle invece le transazioni sono aperte implicitamente.
Differenze tra Delete e Truncate sul Transaction Log
A prescindere dalle dimensioni della tabella, l’operazione Truncate Table avviene in un arco di tempo molto più breve rispetto all’operazione Delete. La motivazione dietro questa differenza di performance riguarda principalmente il fatto che durante la Delete i DBMS scrivono sul relativo Transaction Log tutte le righe eliminate (ciò in alcuni casi può portare anche a problemi di saturazione dello spazio), a differenza della Truncate che crea il log di una singola operazione relativa alla tabella.
Ulteriori differenze con Drop Table
L’istruzione Drop Table (eventualmente seguita da una nuova Create) ha un impatto differente rispetto alla Delete o alla Truncate analizzate nell’articolo. Essa infatti eliminerà la struttura complessiva della tabella, comprensiva ad esempio anche degli indici e dei diritti di scrittura e lettura assegnati ai vari utenti.
Continua a imparare
- Visita la pagina del mio videocorso SQL
- Segui la lezione successiva sulla normalizzazione di un Database
- Torna all’indice delle lezioni