DELETE Vs. TRUNCATE (Nessun server è stato maltrattato durante la scrittura di questo articolo)

Oggi vi voglio parlare di un argomento che mi è stato chiesto tante volte in tanti anni di lavoro con SQL Server.


Che differenza c'è tra il comando T-SQL DELETE ed il comando TRUNCATE?
Quali dei due è meglio utilizzare?
E sopprattutto perche?

Se siete curiosi questa volta andremo più in dettaglio, quindi continuate a leggere. Se avete domande fatele! Se l'articolo vi è piaciuto scrivetelo!

P.S. Come nei migliori gialli il finale si dovrebbe svelare solamente alla fine. Io però ve lo voglio svelare in anticipo per fare in modo che rimanga impresso:
Se dovete cancellare interamente una tabella usato il comando TRUNCATE! Sempre!

Siete pronti? Bene, si parte!

Creaimoci una semplice tabella che chiamiamo Elenco e popoliamola con un po di righe.

       
CREATE TABLE dbo.Elenco
( ID INT IDENTITY(1, 1),
  CODICE VARCHAR(3)
  CONSTRAINT PK_Elenco_ID PRIMARY KEY CLUSTERED (id)
);
        

Poi apriamo una transazione a cui diamo un nome in modo che riconoscibile.

BEGIN TRANSACTION PROVA2

Ed eseguiamo il comando di TRUNCATE che ha questa sintassi:

TRUNCATE TABLE ELENCO

Subito dopo, come nel precedente articolo, interroghiamo il log delle transazioni con la funzione FN_DBLOG

       
SELECT * FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(SELECT [Transaction ID] 
FROM FN_DBLOG(Null,Null)WHERE [Transaction Name]='PROVA2'
)
       

Se osserviamo la parte evidenziata in verde vediamo che indipendetemente da quante righe cancelliamo vengono deallocate direttamente le pagine in memoria.

Qui nell'esempio la pagina 41 (IAM) e tutte le pagine che vanno dalla 280 alla 287 (FPS):

Ora, possiamo anche andare a vedere cosa c'è dentro a queste pagine.

Per farlo dobbiamo abilitare un Flag di traccia, il numero 3604

Poi tramite il comando dbcc page vediamo il contenuto della pagina in memoria 280 cioè

Se scriviamo:

       
DBCC PAGE (0,1,280,1)
       

Guardate cosa otteniamo.. 

Quante informazioni..

• Uno, stiamo leggendo una pagina di dati (m_type=1)
• Due, la pagina è grande 8192 Byte (m_flagbits = 0x8000)
• Tre, il nostro record è grande 18 byte (RecordSize=18)
• Quattro, una pagina conterrà quindi 8192 / 18 = 455 record

Infine vediamo i dati stessi che sono le tre X e le tre Y con cui ho fatto l'insert!

Non scendiamo in altri dettagli ma riassumiamo tutto quello che abbiamo visto sul comando TRUNCATE prima di passare ad analizzare il comando DELETE.

Il comando TRUNCATE è pienamente sostituibile al comando DELETE qual'ora si debba cancellare completamente la tabella.
Il comando TRUNCATE è loggato come gli altri comandi T-SQL e quindi posso fare COMMIT e ROLLBACK.
Il comando TRUNCATE è più molto veloce perchè dealloca direttamente le pagine in memoria.


Adesso vediamo cosa succede se eseguiamo il comdando DELETE.

Supponiamo di avere 100 righe e di cancellarle..

Eseguiamo la stessa Query già fatta per analizzare il comando TRUNCATE

       
SELECT * FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(SELECT [Transaction ID] 
FROM FN_DBLOG(Null,Null)WHERE [Transaction Name]='PROVA2'
)
       

Cosa notiamo per prima cosa?

E' stata creata una riga di log per ogni riga cancellata. Nell'esempio 1000 righe. 

Inoltre vengono generate 1000 richieste di acquisizione di LOCK

HoBt 72057594043695104:ACQUIRE_LOCK_IX OBJECT: 6:773577794:0 ;ACQUIRE_LOCK_IX PAGE: 6:1:280 ;ACQUIRE_LOCK_X KEY: 6:72057594043695104 (8194443284a0)

Ovviamente tutte queste scritture e tutte queste richieste di acquisizione di lock hanno un costo in termini di tempo di esecuzione.

Provate a fare un semplice prova. Prendete la tabella che abbiamo usato in questo articolo e riempitela con dei dati: ad esempio un milione di righe.
Se eseguite la DELETE ci vorranno alcuni secondi per svuotare la tabella.
La TRUNCATE è instantanea.


Per concludere: quando dovete cancellare completamente una tabella usate il comando TRUNCATE


Alla prossima!
Luca

Next post: Query SARGABLE parte 2. Esempi

Previous post: SQL SERVER Express VS. SQL SERVER Standard! (perchè non scegliere la versione Express)