DELETE (Transact-SQL)

Sintassi

[ WITH common_table_expression [ ,...n ] ]
DELETE 
    [ TOP (expression ) [ PERCENT ] ] 
    [ FROM ] 
    { { table_alias
      | <object> 
      | rowset_function_limited 
      [ WITH (table_hint_limited [ ...n ] ) ] } 
      | @table_variable
    }
    [ OUTPUT Clause ]
    [ FROMtable_source [ ,...n ] ] 
    [ WHERE { search_condition 
            | { [ CURRENT OF 
                   { { [ GLOBAL ] cursor_name } 
                       | cursor_variable_name 
                   } 
                ]
              }
            } 
    ] 
    [ OPTION (Query Hint [ ,...n ] ) ] 
[; ]

<object> ::=
{ 
    [ server_name.database_name.schema_name. 
      | database_name. [ schema_name ] . 
      | schema_name.
    ]
    table_or_view_name 
}

Argomenti

• WITH common_table_expression
  Viene specificato il set di risultati denominato temporaneo, anche noto come espressione di tabella comune, definito nell'ambito dell'istruzione DELETE. Il set di risultati deriva da un'istruzione SELECT. Per ulteriori informazioni, vedere WITH common_table_expression (Transact-SQL).

• TOP ( expression) [ PERCENT ]
  Viene specificato il numero o la percentuale di righe casuali che verranno eliminate. expression può essere un numero o una percentuale di righe. Le righe a cui viene fatto riferimento nell'espressione TOP utilizzata con DELETE non sono ordinate.

  Le parentesi che delimitano expression nell'espressione TOP sono necessarie nelle istruzioni INSERT, UPDATE, MERGE e DELETE. Per ulteriori informazioni, vedere TOP (Transact-SQL).

• FROM
  Parola chiave facoltativa che è possibile specificare tra la parola chiave DELETE e l'oggetto di destinazione.

• table_alias
  Alias specificato nella clausola FROM table_source che rappresenta la tabella o la vista da cui devono essere eliminate le righe.

• server_name
  Nome del server collegato in cui si trova la tabella o la vista. È possibile specificare server_name come nome del server collegato oppure con la funzione OPENDATASOURCE.

  Quando server_name viene specificato come server collegato, database_name e schema_name sono obbligatori. Quando server_name viene specificato con OPENDATASOURCE, database_name e schema_name possono non essere validi per tutte le origini dati ed essere soggetti alle funzionalità del provider OLE DB tramite cui viene effettuato l'accesso all'oggetto remoto. Per ulteriori informazioni, vedere Query distribuite.

• database_name
  Nome del database.

• schema_name
  Nome dello schema a cui appartiene la tabella o la vista.

• table_or view_name
  Nome della tabella o della vista da cui si devono rimuovere le righe.

  È necessario che la vista specificata nell'argomento table_or_view_name sia aggiornabile e includa un riferimento a un'unica tabella di base nella clausola FROM della vista. Per ulteriori informazioni sulle viste aggiornabili, vedere CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Funzione OPENQUERY o OPENROWSET, in base alle funzionalità del provider. Per ulteriori informazioni sulle funzionalità necessarie per il provider, vedere Requisiti relativi alle istruzioni UPDATE e DELETE per i provider OLE DB.

• WITH (table_hint_limited [... n] )
  Vengono specificati uno o più hint di tabella consentiti per una tabella di destinazione. La parola chiave WITH e le parentesi sono obbligatorie. Le opzioni NOLOCK e READUNCOMMITTED non sono consentite. Per ulteriori informazioni sugli hint di tabella, vedere Hint di tabella (Transact-SQL).

• @table\_variable
  Viene specificata una variabile di tabella.

• <OUTPUT_Clause>
  Vengono restituite le righe eliminate o le espressioni basate su tali righe nell'ambito dell'operazione di eliminazione. La clausola OUTPUT non è supportata nelle istruzioni DML destinate a viste partizionate locali, viste partizionate distribuite oppure tabelle o viste remote. Per ulteriori informazioni, vedere Clausola OUTPUT (Transact-SQL).

• FROM table_source
  Viene specificata una clausola FROM aggiuntiva che può essere utilizzata per unire in join il parametro table_or view_name di destinazione con <table_source> per identificare le righe da rimuovere. Questa estensione Transact-SQL dell'istruzione DELETE può essere utilizzata al posto di una sottoquery nella clausola WHERE.

  Per ulteriori informazioni, vedere FROM (Transact-SQL).

• WHERE
  Vengono specificate le condizioni utilizzate per limitare il numero di righe da eliminare. Se la clausola WHERE non viene fornita, l'istruzione DELETE consente di eliminare tutte le righe della tabella.

  Le operazioni di eliminazione possono essere di due diversi tipi in base al contenuto della clausola WHERE:

  • Le eliminazioni con ricerca specificano una condizione di ricerca che qualifica le righe da eliminare. Ad esempio, WHERE column_name = value.

  • Le eliminazioni posizionate utilizzano la clausola CURRENT OF per specificare un cursore. L'operazione di eliminazione viene eseguita nella posizione corrente del cursore. Questo tipo di eliminazione risulta più accurato rispetto a un'istruzione DELETE con ricerca che utilizza una clausola WHERE search_condition per qualificare le righe da eliminare. Un'istruzione DELETE con ricerca consente di eliminare più righe se la condizione di ricerca non permette di identificare una singola riga in modo univoco.

• search_condition
  Vengono specificate le condizioni di restrizione per le righe da eliminare. Non sono previsti limiti per il numero di predicati che è possibile includere in una condizione di ricerca. Per ulteriori informazioni, vedere Condizione di ricerca (Transact-SQL).

• CURRENT OF
  Viene specificato che l'istruzione DELETE viene eseguita nella posizione corrente del cursore specificato.

• GLOBAL
  Viene specificato che l'argomento cursor_name consente di fare riferimento a un cursore globale.

• cursor_name
  Nome del cursore aperto da cui viene eseguita l'operazione di recupero. Se esistono sia un cursore globale sia un cursore locale denominati cursor_name, questo argomento consente di fare riferimento al cursore globale se è stato specificato l'argomento GLOBAL. In caso contrario, viene fatto riferimento al cursore locale. Il cursore deve consentire operazioni di aggiornamento.

• cursor_variable_name
  Nome di una variabile di cursore. La variabile di cursore deve fare riferimento a un cursore che consente operazioni di aggiornamento.

• OPTION (query_hint [ ,... n] )
  Parole chiave che indicano che vengono utilizzati hint di ottimizzazione per personalizzare la modalità di elaborazione dell'istruzione nel Motore di database. Per ulteriori informazioni, vedere Hint per la query (Transact-SQL).

Procedure consigliate

Per eliminare tutte le righe di una tabella, utilizzare TRUNCATE TABLE. L'esecuzione di TRUNCATE TABLE è più veloce rispetto a quella di DELETE e comporta un minor utilizzo di risorse del log delle transazioni e di sistema.

Utilizzare la funzione @@ROWCOUNT per restituire il numero di righe eliminate all'applicazione client. Per ulteriori informazioni, vedere @@ROWCOUNT (Transact-SQL).

Informazioni sulla compatibilità

L'utilizzo di SET ROWCOUNT non avrà effetto sulle istruzioni DELETE nella prossima versione di SQL Server. Non utilizzare SET ROWCOUNT con le istruzioni DELETE in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata per utilizzare la sintassi TOP.

Gestione degli errori

È possibile implementare la gestione degli errori per l'istruzione DELETE specificando l'istruzione in un costrutto TRY…CATCH. Per ulteriori informazioni, vedere Utilizzo di TRY...CATCH in Transact-SQL.

L'istruzione DELETE può avere esito negativo se viene violato un trigger o viene effettuato il tentativo di rimozione di una riga a cui fanno riferimento i dati di un'altra tabella contenente un vincolo FOREIGN KEY. Se l'istruzione DELETE tenta di rimuovere più righe e l'eliminazione di una qualsiasi di queste righe viola un trigger o un vincolo, l'istruzione viene annullata, viene restituito un errore e non viene rimossa alcuna riga.

Quando un'istruzione DELETE rileva un errore aritmetico, ovvero un errore di overflow, una divisione per zero o un errore di dominio, durante la valutazione di un'espressione, Motore di database gestisce l'errore come se l'opzione SET ARITHABORT fosse impostata su ON. La parte rimanente del batch viene annullata e viene visualizzato un messaggio di errore.

Interoperabilità

È possibile utilizzare l'istruzione DELETE nel corpo di una funzione definita dall'utente se l'oggetto modificato è una variabile di tabella.

Quando si elimina una riga in cui è contenuta una colonna FILESTREAM, vengono eliminati anche i file del file system sottostanti. I file sottostanti vengono rimossi dal Garbage Collector di FILESTREAM. Per ulteriori informazioni, vedere Gestione di dati FILESTREAM mediante Transact-SQL.

Non è possibile specificare la clausola FROM in un'istruzione DELETE contenente un riferimento diretto o indiretto a una vista per cui è stato definito un trigger INSTEAD OF. Per ulteriori informazioni sui trigger INSTEAD OF, vedere CREATE TRIGGER (Transact-SQL).

Limitazioni e restrizioni

Quando l'espressione TOP viene utilizzata con DELETE, le righe a cui si fa riferimento non sono ordinate e la clausola ORDER BY non può essere specificata direttamente in questa istruzione. Se è necessario utilizzare l'espressione TOP per eliminare righe in un ordine cronologico significativo, tale espressione deve essere utilizzata insieme a una clausola ORDER BY in un'istruzione di selezione secondaria. Vedere la sezione Esempi più avanti in questo argomento.

L'espressione TOP non può essere utilizzata in un'istruzione DELETE rispetto a viste partizionate.

L'impostazione dell'opzione SET ROWCOUNT viene ignorata per le istruzioni DELETE eseguite in tabelle remote e in viste partizionate locali e remote.

Comportamento di blocco

Quando si eliminano righe da un heap, nel Motore di database può essere utilizzato il blocco di riga o di pagina per l'operazione. Le pagine svuotate dall'operazione di eliminazione rimangono pertanto allocate all'heap. Se le pagine vuote non vengono deallocate, non è possibile riutilizzare lo spazio associato per altri oggetti nel database. Per eliminare le righe di un heap e deallocare le pagine, utilizzare uno dei metodi seguenti.

• Specificare l'hint TABLOCK nell'istruzione DELETE. Se si utilizza l'hint TABLOCK, l'operazione di eliminazione acquisirà un blocco condiviso sulla tabella anziché un blocco di riga o di pagina. In questo modo sarà possibile deallocare le pagine. Per ulteriori informazioni sull'hint TABLOCK, vedere Hint di tabella (Transact-SQL).

• Utilizzare TRUNCATE TABLE se è necessario eliminare tutte le righe della tabella.

• Creare un indice cluster sull'heap prima di eliminare le righe. È possibile eliminare l'indice cluster dopo l'eliminazione delle righe. Questo metodo richiede più tempo rispetto ai precedenti e l'utilizzo di una maggiore quantità di risorse temporanee.

Per ulteriori informazioni sui blocchi, vedere Utilizzo dei blocchi in Motore di database.

Comportamento di registrazione

L'istruzione DELETE viene sempre registrata completamente.

Autorizzazioni

Sono richieste le autorizzazioni DELETE per la tabella di destinazione. Se nell'istruzione è contenuta una clausola WHERE, sono inoltre richieste le autorizzazioni SELECT.

Le autorizzazioni DELETE vengono concesse per impostazione predefinita ai membri del ruolo predefinito del server sysadmin, ai membri dei ruoli predefiniti del database db_owner e db_datawriter e al proprietario della tabella. I membri dei ruoli sysadmin, db_owner e db_securityadmin e il proprietario della tabella possono trasferire le autorizzazioni ad altri utenti.

Esempi

Sintassi di base

Negli esempi di questa sezione vengono illustrate le funzionalità di base dell'istruzione DELETE tramite la sintassi minima richiesta.

Nell'esempio seguente vengono eliminate tutte le righe dalla tabella SalesPersonQuotaHistory poiché non viene utilizzata una clausola WHERE per limitare il numero di righe eliminate.

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

Specifica di righe da eliminare

Negli esempi di questa sezione vengono illustrate le modalità per limitare il numero di righe eliminate.

A. Utilizzo della clausola WHERE per limitare le righe

USE AdventureWorks;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO

B. Utilizzo della clausola TOP

USE AdventureWorks;
GO
DELETE TOP (2.5) PERCENT 
FROM Production.ProductInventory;
GO

USE AdventureWorks;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

USE AdventureWorks;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
   (SELECT TOP 10 PurchaseOrderDetailID 
    FROM Purchasing.PurchaseOrderDetail 
    ORDER BY DueDate ASC);
GO

C. Utilizzo di un cursore per eliminare la riga corrente

USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.EmployeeID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

D. Utilizzo di una sottoquery e dell'estensione FROM di Transact-SQL

-- SQL-2003 Standard subquery

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE SalesPersonID IN 
    (SELECT SalesPersonID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);
GO

-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO

E. Utilizzo di un'espressione di tabella comune

Specifica di oggetti di destinazione diversi dalle tabelle standard

Negli esempi di questa sezione viene illustrato come eliminare righe specificando una vista o una variabile di tabella.

Eliminazione di righe da una tabella remota

Negli esempi di questa sezione viene illustrato come eliminare righe da una tabella di destinazione remota tramite un server collegato o una funzione per i set di righe per fare riferimento alla tabella remota.

Override del comportamento predefinito di Query Optimizer tramite hint

Negli esempi di questa sezione viene illustrato come utilizzare gli hint di tabella e di query per eseguire temporaneamente l'override del comportamento predefinito di Query Optimizer durante l'elaborazione dell'istruzione DELETE.

Attenzione
Poiché Query Optimizer di SQL Server consente in genere di selezionare il piano di esecuzione migliore per una query, gli hint devono essere utilizzati da sviluppatori e amministratori di database esperti solo se strettamente necessario.

Nell'esempio seguente viene specificato l'hint di tabella READPAST. Se quest'ultimo viene specificato, vengono ignorati sia i blocchi a livello di riga sia a livello di pagina, pertanto le righe e le pagine bloccate da altre transazioni non vengono lette dal Motore di database. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

Acquisizione dei risultati dell'istruzione DELETE

Negli esempi di questa sezione viene illustrato come utilizzare la clausola OUTPUT per restituire informazioni da (o espressioni basate su) ogni riga interessata da un'istruzione DELETE. Questi risultati possono essere restituiti all'applicazione di elaborazione per essere utilizzati in elementi quali messaggi di conferma, archiviazione e altri requisiti dell'applicazione simili.

A. Utilizzo di DELETE con la clausola OUTPUT

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify all rows in the table that match the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

B. Utilizzo di OUTPUT con from_table_name in un'istruzione DELETE

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID 
FROM @MyTableVar
ORDER BY ProductModelID;
GO

Vedere anche

Riferimento

Concetti