DELETE (Transact-SQL)

Rimuove una o più righe da una tabella o vista in SQL Server 2012.

Convenzioni della sintassi 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> ]
    [ FROM table_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>
  Specifica 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.

  Le espressioni di tabella comuni possono inoltre essere utilizzate con istruzioni SELECT, INSERT, UPDATE e CREATE VIEW. Per ulteriori informazioni, vedere WITH Common.

• TOP (expression) [ PERCENT ]
  Viene specificato il numero o la percentuale di righe casuali che verranno eliminate. Il valore di expression può essere specificato come numero o come percentuale di righe. Le righe a cui viene fatto riferimento nell'espressione TOP utilizzata con INSERT, UPDATE o DELETE non sono disposte in alcun ordine. 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_or_view_name o rowset_function_limited.

• 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 (che utilizza come nome un nome di server collegato o la funzione OPENDATASOURCE) in cui è contenuta la tabella o la vista. Se si specifica server_name, database_name e schema_name sono necessari.

• 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 desidera rimuovere le righe.

  È inoltre possibile utilizzare una variabile di tabella, nel relativo ambito, come origine della tabella in un'istruzione DELETE.

  È necessario che la vista a cui viene fatto riferimento in table_or_view_name sia aggiornabile e includa un riferimento esatto a una tabella di base nella clausola FROM della definizione 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.

• WITH ( <table_hint_limited> [... n] )
  Specifica 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).

• <OUTPUT_Clause>
  Restituisce le righe eliminate o le espressioni basate su tali righe nell'ambito di un'operazione DELETE. La clausola OUTPUT non è supportata nelle istruzioni DML eseguite su viste o tabelle remote. Per ulteriori informazioni, vedere Clausola OUTPUT (Transact-SQL).

• FROM table_source
  Specifica una clausola FROM aggiuntiva. Questa estensione Transact-SQL dell'istruzione DELETE consente di specificare dati di <table_source> e di eliminare le righe corrispondenti dalla tabella specificata nella prima clausola FROM.

  È possibile utilizzare questa estensione, specificando un join, al posto di una sottoquery nella clausola WHERE per identificare le righe che si desidera rimuovere.

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

• WHERE
  Specifica le condizioni utilizzate per limitare il numero di righe da eliminare. Se la clausola WHERE viene omessa, l'istruzione DELETE elimina 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 elimina più righe se la condizione di ricerca non identifica una singola riga in modo univoco.

• <search_condition>
  Specifica 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
  Specifica che l'istruzione DELETE viene eseguita nella posizione corrente del cursore specificato.

• GLOBAL
  Specifica che l'argomento cursor_name fa riferimento a un cursore globale.

• cursor_name
  Nome del cursore aperto da cui viene eseguita l'operazione di recupero. Se sono presenti un cursore globale e un cursore locale denominati cursor_name, questo argomento fa riferimento al cursore globale se è stato specificato l'argomento GLOBAL. In caso contrario, fa 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 quali hint di ottimizzazione vengono utilizzati 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 in una tabella, utilizzare TRUNCATE TABLE. L'esecuzione di TRUNCATE TABLE è più rapida 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 nell'applicazione client. Per ulteriori informazioni, vedere @@ROWCOUNT (Transact-SQL).

Gestione degli errori

È possibile implementare la gestione degli errori per l'istruzione DELETE specificando l'istruzione in un costrutto TRY…CATCH.

L'istruzione DELETE può avere esito negativo se viola un trigger o tenta di rimuovere 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 restituito 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 che contiene 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 Accedere a Dati FILESTREAM con 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

Se TOP viene utilizzato con DELETE, le righe a cui viene fatto riferimento non vengono disposte in alcun ordine e la clausola ORDER BY non può essere specificata in modo diretto in questa istruzione. Se si desidera utilizzare TOP per eliminare le righe in un ordine cronologico significativo, è necessario utilizzare TOP insieme a una clausola ORDER BY in un'istruzione sub-SELECT. Vedere la sezione Esempi più avanti in questo argomento.

Non è possibile utilizzare TOP in un'istruzione DELETE in viste partizionate.

Comportamento di blocco

Per impostazione predefinita, un'istruzione DELETE acquisisce sempre un blocco esclusivo (X) sulla tabella che modifica e mantiene tale blocco fino al completamento della transazione. Con un blocco esclusivo (X), nessun'altra transazione può modificare dati. Le operazioni di lettura possono essere eseguite solo utilizzando l'hint NOLOCK o il livello di isolamento Read Uncommitted. È possibile specificare hint di tabella per eseguire l'override di questo comportamento predefinito per la durata dell'istruzione DELETE specificando un altro metodo di blocco. Gli hint dovrebbero comunque essere utilizzati solo se strettamente necessario ed esclusivamente da sviluppatori e amministratori di database esperti. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

Quando si eliminano righe da un heap, il Motore di database può utilizzare 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, nell'operazione di eliminazione viene accettato un blocco esclusivo nella 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 utilizza una maggiore quantità di risorse temporanee.

Comportamento di registrazione

L'istruzione DELETE viene sempre registrata completamente.

Sicurezza

Autorizzazioni

È necessario disporre delle autorizzazioni DELETE per la tabella di destinazione. Se l'istruzione contiene una clausola WHERE, sono inoltre richieste le autorizzazioni SELECT.

Le autorizzazioni DELETE vengono assegnate per impostazione predefinita ai membri del ruolo predefinito del server sysadmin e ai membri dei ruoli predefiniti del database db_owner e db_datawriter nonché 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 contenuti in questa sezione vengono illustrate le funzionalità di base dell'istruzione DELETE tramite la sintassi minima richiesta.

A.Utilizzo di DELETE senza una clausola WHERE

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

USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

Limitazione delle righe eliminate

Negli esempi riportati in questa sezione viene illustrato come limitare il numero di righe che verranno eliminate.

A.Utilizzo della clausola WHERE per eliminare un set di righe

Nell'esempio seguente vengono eliminate dalla tabella ProductCostHistory tutte le righe in cui il valore della colonna StandardCost è maggiore di 1000.00.

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

Nell'esempio seguente viene illustrata una clausola WHERE più complessa. La clausola WHERE definisce due condizioni che devono essere soddisfatte per determinare le righe da eliminare. Il valore nella colonna StandardCost deve essere compreso tra 12.00 e 14.00, mentre quello nella colonna SellEndDate deve essere Null. Nell'esempio viene inoltre stampato il valore dalla funzione @@ROWCOUNT per restituire il numero di righe eliminate.

USE AdventureWorks2012;
GO
DELETE Production.ProductCostHistory
WHERE StandardCost BETWEEN 12.00 AND 14.00
      AND EndDate IS NULL;
PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));

B.Utilizzo di un cursore per determinare la riga da eliminare

Nell'esempio seguente viene eliminata una riga dalla tabella EmployeePayHistory tramite un cursore denominato my_cursor. L'operazione di eliminazione interessa unicamente la riga attualmente recuperata dal cursore.

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

C.Utilizzo di join e sottoquery per i dati di una tabella per eliminare righe di un'altra tabella

Negli esempi seguenti vengono illustrati due modi per eliminare righe di una tabella in base ai dati di un'altra tabella. In entrambi gli esempi le righe della tabella SalesPersonQuotaHistory vengono eliminate in base alle vendite realizzate dall'inizio dell'anno archiviate nella tabella SalesPerson. La prima istruzione DELETE illustra la soluzione di sottoquery compatibile con ISO, mentre la seconda istruzione DELETE illustra l'estensione FROM Transact-SQL per creare un join delle due tabelle.

-- SQL-2003 Standard subquery

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

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

A.Utilizzo di TOP per limitare il numero di righe eliminate

Quando si utilizza una clausola TOP (n) con l'istruzione DELETE, l'operazione di eliminazione viene eseguita su una selezione casuale di un numero n di righe. Nell'esempio seguente vengono eliminate 20 righe casuali dalla tabella PurchaseOrderDetail con scadenze precedenti al 1 luglio 2006.

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

Se si desidera utilizzare TOP per eliminare righe in un ordine cronologico significativo, è necessario utilizzare questa clausola insieme a ORDER BY in un'istruzione sub-SELECT. Tramite la query seguente vengono eliminate le 10 righe della tabella PurchaseOrderDetail contenenti le date di scadenza più imminenti. Per assicurarsi che vengano eliminate solo 10 righe, la colonna specificata nell'istruzione di selezione secondaria (PurchaseOrderID) è la chiave primaria della tabella. L'utilizzo di una colonna non chiave nell'istruzione sub-SELECT può avere come conseguenza l'eliminazione di più di 10 righe se la colonna specificata contiene valori duplicati.

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

Eliminazione di righe da una tabella remota

Negli esempi riportati in questa sezione viene illustrato come eliminare righe da una tabella remota tramite un server collegato o una funzione per i set di righe per fare riferimento alla tabella remota. Esiste una tabella remota in un server diverso o un'istanza di SQL Server.

A.Eliminazione di dati da una tabella remota tramite un server collegato

Nell'esempio seguente vengono eliminate righe da una tabella remota. L'esempio inizia con la creazione di un collegamento all'origine dati remota tramite sp_addlinkedserver. Il nome del server collegato, MyLinkServer, viene successivamente specificato come parte del nome di oggetto in quattro parti nel formato server.catalog.schema.object.

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'server_name',
    @catalog = N'AdventureWorks2012';
GO

-- Specify the remote data source using a four-part name 
-- in the form linked_server.catalog.schema.object.

DELETE MyLinkServer.AdventureWorks2012.HumanResources.Department WHERE DepartmentID > 16;
GO

B.Eliminazione di dati da una tabella remota tramite una funzione OPENQUERY

Nell'esempio seguente vengono eliminate righe da una tabella remota specificando la funzione per i set di righe OPENQUERY. Viene utilizzato il nome del server collegato creato nell'esempio precedente.

DELETE OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 18');
GO

C.Eliminazione di dati da una tabella remota tramite una funzione OPENDATASOURCE

Nell'esempio seguente vengono eliminate righe da una tabella remota specificando la funzione per i set di righe OPENDATASOURCE. Specificare un nome di server valido per l'origine dati utilizzando il formato server_name o server_name\instance_name.

DELETE FROM OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks2012.HumanResources.Department 
WHERE DepartmentID = 17;'

Acquisizione dei risultati dell'istruzione DELETE

A.Utilizzo di DELETE con la clausola OUTPUT

Nell'esempio seguente viene illustrato come salvare i risultati di un'istruzione DELETE in una variabile di tabella.

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

--Verify the rows in the table matching 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

Nell'esempio seguente vengono eliminate alcune righe nella tabella ProductProductPhoto in base ai criteri di ricerca definiti nella clausola FROM dell'istruzione DELETE. La clausola OUTPUT restituisce le colonne della tabella che si desidera eliminare, DELETED.ProductID, DELETED.ProductPhotoID e alcune colonne della tabella Product. Queste informazioni vengono utilizzate nella clausola FROM per specificare le righe da eliminare.

USE AdventureWorks2012;
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

CREATE TRIGGER (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

UPDATE (Transact-SQL)

WITH common_table_expression (Transact-SQL)

@@ROWCOUNT (Transact-SQL)