Prima di iniziare con qualche esempio, vorrei cercare di definire la differenza sostanziale tra i due comandi. Sono ambedue istruzioni pensate per la cancellazione di dati; la DELETE registra nel transaction log l’eliminazione di ogni singola riga, viceversa TRUNCATE registra un solo log risultando così generalmente più performante (questo tra l’altro è il motivo per cui non fa scattare l’esecuzione di triggers).
Vi esorto a sfogliare la documentazione ufficiale su MSDN che fornisce ben più ampi dettagli (DELETE, TRUNCATE).
Concentriamoci ora sul solito esempio esplicativo che ci permette subito di sporcarci le mani con i due comandi.
Creiamo la classica tabella di test
CREATE TABLE MyTable (
[Id] INT IDENTITY(1, 1),
[Email] NVARCHAR(256) NOT NULL,
[Password] NVARCHAR(256) NOT NULL,
[Salt] INT NOT NULL
)
GO
insert into MyTable (Email, Password, Salt)
select
convert(nvarchar,o1.object_id)+'@email.it',
'pwd-'+convert(nvarchar,o1.object_id),
checksum(newid())
from
sys.objects o1, sys.objects o2
-- (9801 rows affected)
Nel mio caso 9801 righe inserite.
Passiamo ora alle eliminazioni.
Eliminazione con e senza clausola WHERE
delete from MyTable where Id % 2 = 0; -- elimina tutti gli elementi con id pari
-- (4901 rows affected)
delete from MyTable; -- tutti i records ancora presenti in tabella
-- (4900 rows affected)
Eliminazione di un numero definito di elementi (TOP)
delete top (100) from MyTable; -- elimina solo 100 elementi dalla tabella
-- (100 rows affected)
delete top (80) percent from MyTable; -- oppure possiamo chiedere l'eliminazione dell'80% dei dati
-- (7841 rows affected) (NB. 9801 * 0.8 = 7840.8)
Eliminazione da CTE
with cte as (
select *
from MyTable
where Id % 2 = 0
)
delete from cte;
-- (4900 rows affected)
Truncate dell’intera tabella
Ripristiniamo ora i dati ricreando la tabella da zero. Vorrei far notare un importante particolare di questo comando.
Innanzitutto chiediamo le informazioni dell’identity della tabella
dbcc checkident('MyTable', noreseed);
-- Checking identity information: current identity value '9801', current column value '9801'.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Vediamo che ha come valore corrente il count della nostra tabella 9801 (NB. L’identity parte da 1).
Eseguiamo ora il truncate e di seguito chiediamo nuovamente i metadati dell’identity.
truncate table MyTable;
-- Commands completed successfully.
dbcc checkident('MyTable', noreseed);
-- Checking identity information: current identity value 'NULL', current column value 'NULL'.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Interessante notare come l’output non abbia restituito un numero di righe e soprattutto che l’identity della nostra tabella è stata resettata.