differenze tra delete e truncate in sql

Importanti differenze tra Delete e Truncate

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 della 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. La situazione non è semplicissima, perché queste differenze possono variare da un DBMS all’altro.

 

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. Quindi dopo una Truncate i nuovi valori di una colonna Identity ricominceranno sempre dal valore iniziale (solitamente 1), mentre la Delete proseguirà considerando i valori inseriti prima della rimozione delle righe.

Eseguiamo su un database Sql Server di test questo codice:

  1. inseriamo una prima riga nella tabella INSERT INTO TabellaConIdentity(Colonna2) VALUES (‘Test’);
  2. otterremo in automatico il valore 1 nella prima colonna di tipo Identity;
    Colonna1 Colonna2
    1 Test
  3. eliminiamo la riga con una delete DELETE FROM TabellaConIdentity: la tabella sarà vuota;
  4. inseriamo una nuova riga con lo stesso codice del punto 1, otterremo nella prima colonna il valore 2;
    Colonna1 Colonna2
    2 Test
  5. eliminiamo ora i dati con una truncate TRUNCATE TABLE TabellaConIdentity. Anche in questo caso la tabella sarà vuota;
  6. 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

Invece 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, eseguire una Truncate ha l’effetto di eseguire un commit implicito di tutte le transazioni attive nella sezione corrente. Questo comportamento è condiviso da altri statement, in questo articolo trovate un approfondimento a riguardo https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/implicit-commit.html. 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 ad 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 COMMIT 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 logga solo l’operazione relativa alla tabella.

 

Ulteriori differenze

Ricordiamo in conclusione dell’articolo che l’operazione di Drop e Create della tabella ha un impatto differente rispetto alla Delete o alla Truncate analizzate nell’articolo. L’istruzione DROP TABLE eliminerà la struttura complessiva della tabella, comprensiva ad esempio anche degli indici e dei privilegi assegnati all’utente.

In questa pagina trovi un articolo su ulteriori differenze tra Oracle e Sql Server.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

Torna su