I migliori modi per usare l’istruzione di cancellazione SQL in una tabella SQL

Nei database relazionali, creiamo tabelle per memorizzare dati in vari formati. SQL Server memorizza i dati in un formato di riga e colonna che contiene un valore associato ad ogni tipo di dati. Quando progettiamo le tabelle SQL, definiamo tipi di dati come interi, float, decimali, varchar e bit. Per esempio, una tabella che memorizza i dati dei clienti potrebbe avere campi come nome del cliente, email, indirizzo, stato, paese e così via. Vari comandi SQL vengono eseguiti su una tabella SQL e possono essere divisi nelle seguenti categorie:

• Data Definition Language (DDL): Questi comandi sono usati per creare e modificare gli oggetti di un database.
  • Create: Crea oggetti
  • Alter: Modifica gli oggetti
  • Drop: Elimina gli oggetti
  • Truncate: Cancella tutti i dati da una tabella
• Data Manipulation Language (DML): Questi comandi inseriscono, recuperano, modificano, cancellano e aggiornano i dati nel database.
  • Seleziona: Recupera dati da una tabella singola o multipla
  • Insert: Aggiunge nuovi dati in una tabella
  • Aggiorna: Modifica i dati esistenti
  • Elimina: Cancella i record esistenti in una tabella

• Data Control Language (DCL): Questi comandi sono associati ai controlli dei diritti o dei permessi in un database.
  • Grant: Assegna permessi ad un utente
  • Revoke: Revoca i permessi ad un utente
• Transaction Control Language (TCL): Questi comandi controllano le transazioni in un database.
  • Commit: Salva i cambiamenti fatti dalla query
  • Rollback: Riporta una transazione esplicita o implicita all’inizio della transazione, o ad un punto di salvataggio all’interno della transazione
  • Salva transazioni: Imposta un punto di salvataggio o un marcatore all’interno di una transazione

Supponiamo che tu abbia i dati degli ordini dei clienti memorizzati in una tabella SQL. Se continuate a inserire dati in questa tabella continuamente, la tabella potrebbe contenere milioni di record, il che causerebbe problemi di prestazioni nelle vostre applicazioni. Anche la manutenzione degli indici potrebbe diventare estremamente dispendiosa in termini di tempo. Spesso non avete bisogno di conservare ordini più vecchi di tre anni. In questi casi, potreste cancellare quei record dalla tabella. Questo risparmierebbe spazio di memoria e ridurrebbe gli sforzi di manutenzione.

Puoi rimuovere i dati da una tabella SQL in due modi:

• Utilizzando un’istruzione di cancellazione SQL
• Utilizzando un truncate

Vedremo la differenza tra questi comandi SQL più avanti. Esploriamo prima l’istruzione di cancellazione SQL.

Un’istruzione di cancellazione SQL senza alcuna condizione

Nelle istruzioni del linguaggio di manipolazione dei dati (DML), un’istruzione di cancellazione SQL rimuove le righe da una tabella. Potete cancellare una riga specifica o tutte le righe. Un’istruzione di cancellazione di base non richiede alcun argomento.

Creiamo una tabella Orders SQL utilizzando lo script seguente. Questa tabella ha tre colonne , e .

Create Table Orders ( OrderID int, ProductName varchar(50), ProductQuantity int )

Inseriamo alcuni record in questa tabella.

Insert into Orders values (1,'ABC books',10), (2,'XYZ',100), (3,'SQL book',50)

Ora, supponiamo di voler eliminare i dati della tabella. Potete specificare il nome della tabella per rimuovere i dati usando l’istruzione delete. Entrambe le istruzioni SQL sono uguali. Possiamo specificare il nome della tabella dalla parola chiave (opzionale) o specificare il nome della tabella direttamente dopo l’istruzione delete.

Delete Orders Go Delete from Orders GO

Un’istruzione delete SQL con dati filtrati

Queste istruzioni delete SQL eliminano tutti i dati della tabella. Di solito, non rimuoviamo tutte le righe da una tabella SQL. Per rimuovere una riga specifica, possiamo aggiungere una clausola where con l’istruzione di cancellazione. La clausola where contiene i criteri di filtro e alla fine determina quale riga (o righe) rimuovere.

Per esempio, supponiamo di voler rimuovere l’ordine id 1. Una volta aggiunta una clausola where, SQL Server controlla prima le righe corrispondenti e rimuove quelle specifiche righe.

Delete Orders where orderid=1

Se la condizione della clausola where è falsa, non rimuove nessuna riga. Per esempio, abbiamo rimosso l’ordine 1 dalla tabella degli ordini. Se eseguiamo di nuovo l’istruzione, non trova nessuna riga che soddisfi la condizione della clausola where. In questo caso, restituisce 0 righe interessate.

Dichiarazione di cancellazione SQL e clausola TOP

Puoi usare anche la dichiarazione TOP per cancellare le righe. Per esempio, la seguente query elimina le prime 100 righe dalla tabella Ordini.

Delete top (100)  from Orders

Siccome non abbiamo specificato nessun ‘ORDER BY’, sceglie delle righe a caso e le elimina. Possiamo utilizzare la clausola Order by per ordinare i dati ed eliminare le prime righe. Nella query seguente, ordina i dati in ordine decrescente e poi li elimina dalla tabella.

Delete from Orders where In ( Select top 100 FROM Orders order by Desc )

Eliminazione delle righe basate su un’altra tabella

A volte abbiamo bisogno di eliminare le righe basate su un’altra tabella. Questa tabella potrebbe esistere nello stesso database oppure no.

• Table lookup

Possiamo usare il metodo table lookup o SQL join per eliminare queste righe. Per esempio, vogliamo eliminare le righe dalla tabella che soddisfano la seguente condizione:

Dovrebbe avere righe corrispondenti nella tabella .

Guardate la query sottostante, qui abbiamo un’istruzione select nella clausola where dell’istruzione delete. SQL Server prima ottiene le righe che soddisfano l’istruzione select e poi rimuove quelle righe dalla tabella usando l’istruzione di cancellazione SQL.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

In alternativa, possiamo usare SQL join tra queste tabelle e rimuovere le righe. Nella query seguente, uniamo le tabelle ] con la tabella. Un join SQL lavora sempre su una colonna comune tra le tabelle. Abbiamo una colonna che unisce entrambe le tabelle insieme.

DELETE Orders FROM Orders o INNER JOIN Customer c ON o.orderid=c.orderid

Per capire la dichiarazione di cancellazione di cui sopra, vediamo il piano di esecuzione attuale.

Come da piano di esecuzione, esegue una scansione della tabella su entrambe le tabelle, ottiene i dati corrispondenti e li elimina dalla tabella Ordini.

• Common table expression (CTE)

Possiamo usare una common table expression (CTE) anche per cancellare le righe da una tabella SQL. Per prima cosa, definiamo una CTE per trovare la riga che vogliamo rimuovere.

Poi, uniamo la CTE con gli ordini della tabella SQL e cancelliamo le righe.

WITH cteOrders AS (SELECT OrderID FROM Customer WHERE CustomerID = 1 ) DELETE Orders FROM cteOrders sp INNER JOIN dbo.Orders o ON o.orderid = sp.orderid;

Impatti sull’intervallo di identità

Le colonne di identità in SQL Server generano valori unici e sequenziali per la vostra colonna. Sono utilizzate principalmente per identificare in modo univoco una riga nella tabella SQL. Una colonna chiave primaria è anche una buona scelta per un indice clustered in SQL Server.

Nello script seguente, abbiamo una tabella. Questa tabella ha una colonna di identità id.

Create Table Employee ( id int identity(1,1),  varchar(50) )

Abbiamo inserito 50 record in questa tabella che ha generato i valori di identità per la colonna id.

Declare @id int=1 While(@id<=50) BEGIN Insert into Employee() values('Test'+CONVERT(VARCHAR,@ID)) Set @id=@id+1 END

Se cancelliamo alcune righe dalla tabella SQL, non resetta i valori di identità per i valori successivi. Per esempio, cancelliamo alcune righe che hanno valori di identità da 20 a 25.

Delete from employee where id between 20 and 25

Ora, visualizzate i record della tabella.

Select * from employee where id>15

Mostra lo scarto nell’intervallo dei valori di identità.

L’istruzione di cancellazione SQL e il log delle transazioni

La cancellazione SQL registra ogni cancellazione di riga nel log delle transazioni. Supponiamo che abbiate bisogno di cancellare milioni di record da una tabella SQL. Non volete cancellare un gran numero di record in una singola transazione perché potrebbe far crescere esponenzialmente il vostro file di log e il vostro database potrebbe anche non essere disponibile. Se si annulla una transazione nel mezzo, potrebbero volerci ore per eseguire il rollback di una dichiarazione di cancellazione.

In questo caso, si dovrebbe sempre cancellare le righe in piccoli pezzi e impegnare quei pezzi regolarmente. Per esempio, si può cancellare un batch di 10.000 righe alla volta, impegnarlo e passare al batch successivo. Quando SQL Server esegue il commit del chunk, la crescita del log delle transazioni può essere controllata.

Migliori pratiche

• Si dovrebbe sempre eseguire un backup prima di eliminare i dati.
• Per default, SQL Server usa transazioni implicite e esegue il commit dei record senza chiedere all’utente. Come migliore pratica, dovreste iniziare una transazione esplicita usando Begin Transaction. Vi dà il controllo di commettere o rollback della transazione. Dovresti anche eseguire frequenti backup del log delle transazioni se il tuo database è in modalità di recupero completo.
• Vuoi cancellare i dati in piccoli pezzi per evitare un uso eccessivo del log delle transazioni. Evita anche i blocchi per altre transazioni SQL.
• Dovresti limitare i permessi in modo che gli utenti non possano cancellare i dati. Solo gli utenti autorizzati dovrebbero avere accesso alla rimozione dei dati da una tabella SQL.
• Si vuole eseguire l’istruzione di cancellazione con una clausola where. Rimuove i dati filtrati da una tabella SQL. Se la vostra applicazione richiede frequenti cancellazioni di dati, è una buona idea resettare i valori di identità periodicamente. Altrimenti, potreste trovarvi di fronte a problemi di esaurimento dei valori di identità.
• Nel caso vogliate svuotare la tabella, è consigliabile usare l’istruzione truncate. L’istruzione truncate rimuove tutti i dati da una tabella, usa una registrazione minima delle transazioni, azzera l’intervallo dei valori di identità, ed è più veloce dell’istruzione di cancellazione SQL perché dealloca immediatamente tutte le pagine della tabella.
• Nel caso in cui usiate vincoli di chiave esterna (relazione genitore-figlio) per le vostre tabelle, dovreste cancellare la riga da una riga figlia e poi dalla tabella madre. Se cancelli la riga dalla riga padre, puoi anche usare l’opzione cascade on delete per cancellare automaticamente la riga da una tabella figlia. Puoi fare riferimento all’articolo Eliminazione a cascata e aggiornamento a cascata in SQL Server foreign key per ulteriori approfondimenti.
• Se si utilizza la clausola top per eliminare le righe, SQL Server elimina le righe in modo casuale. Dovreste sempre usare la clausola top con la corrispondente clausola Order by e Group by.
• Una dichiarazione di cancellazione acquisisce un blocco esclusivo sulla tabella di riferimento; quindi, durante questo periodo, nessun’altra transazione può modificare i dati. Potete usare il suggerimento NOLOCK per leggere i dati.
• Si dovrebbe evitare di usare il table hint per sovrascrivere il comportamento di blocco predefinito dell’istruzione di cancellazione SQL; dovrebbe essere usato solo da DBA e sviluppatori esperti.

Considerazioni importanti

Ci sono molti benefici nell’usare le istruzioni di cancellazione SQL per rimuovere i dati da una tabella SQL, ma come potete vedere, richiede un approccio metodico. È importante cancellare sempre i dati in piccoli lotti e procedere con cautela quando si cancellano i dati da un’istanza di produzione. Avere una strategia di backup per recuperare i dati nel minor tempo possibile è un must per evitare tempi di inattività o impatti sulle prestazioni future.