DELETE (Transact-SQL)

Syntax

[ 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 
}

Argumente

• WITH common_table_expression
  Gibt das auch als allgemeiner Tabellenausdruck bezeichnete temporäre, benannte Resultset an, das im Rahmen der DELETE-Anweisung definiert wurde. Das Resultset wird von einer SELECT-Anweisung abgeleitet. Weitere Informationen finden Sie unter WITH common_table_expression (Transact-SQL).

• TOP ( expression) [ PERCENT ]
  Gibt die Anzahl oder den Prozentsatz zufälliger Zeilen an, die gelöscht werden. expression kann entweder eine Anzahl oder ein Prozentsatz der Zeilen sein. Die Zeilen, auf die in einem mit DELETE verwendeten TOP-Ausdruck verwiesen wird, sind nicht in einer bestimmten Reihenfolge angeordnet.

  In den Anweisungen INSERT, UPDATE, MERGE und DELETE sind Klammern erforderlich, die expression in TOP begrenzen. Weitere Informationen finden Sie unter TOP (Transact-SQL).

• FROM
  Ein optionales Schlüsselwort, das zwischen dem DELETE-Schlüsselwort und dem Zielobjekt verwendet werden kann.

• table_alias
  Der in der FROMtable_source-Klausel angegebene Alias, der die Tabelle oder Sicht darstellt, aus der die Zeilen gelöscht werden sollen.

• server_name
  Der Name des Verbindungsservers, auf dem sich die Tabelle oder Sicht befindet. server_name kann als Name eines Verbindungsservers oder mithilfe der OPENDATASOURCE-Funktion angegeben werden.

  Wenn server_name als Verbindungsserver angegeben wird, sind database_name und schema_name erforderlich. Wenn server_name mit OPENDATASOURCE angegeben wird, gelten database_name und schema_name möglicherweise nicht für alle Datenquellen und unterliegen den Funktionen des OLE DB-Anbieters, der auf das Remoteobjekt zugreift. Weitere Informationen finden Sie unter Verteilte Abfragen.

• database_name
  Der Name der Datenbank.

• schema_name
  Der Name des Schemas, dem die Tabelle oder Sicht angehört.

• table_or view_name
  Der Name der Tabelle oder Sicht, aus der die Zeilen entfernt werden sollen.

  Die Sicht, auf die table_or_view_name verweist, muss aktualisierbar sein und auf genau eine Basistabelle in der FROM-Klausel der Sicht verweisen. Weitere Informationen zu aktualisierbaren Sichten finden Sie unter CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Die Funktion OPENQUERY oder OPENROWSET, die der Funktionalität des Anbieters unterliegt. Weitere Informationen zur Funktionalität, die für den Anbieter erforderlich ist, finden Sie unter UPDATE- und DELETE-Anforderungen für OLE DB-Anbieter.

• WITH (table_hint_limited [... n] )
  Gibt mindestens einen Tabellenhinweis an, der für eine Zieltabelle zulässig ist. Das WITH-Schlüsselwort und die Klammern sind erforderlich. NOLOCK und READUNCOMMITTED sind nicht zulässig. Weitere Informationen zu Tabellenhinweisen finden Sie unter Tabellenhinweise (Transact-SQL).

• @table\_variable
  Gibt eine Tabelle-Variable an.

• <OUTPUT_Clause>
  Gibt gelöschte Zeilen bzw. auf diesen basierende Ausdrücke als Teil des Löschvorgangs zurück. Die OUTPUT-Klausel wird in DML-Anweisungen, die auf lokale partitionierte Sichten, verteilte partitionierte Sichten, Remotetabellen oder Remotesichten ausgerichtet sind, nicht unterstützt. Weitere Informationen finden Sie unter OUTPUT-Klausel (Transact-SQL).

• FROM table_source
  Gibt eine zusätzliche FROM-Klausel an, mit der table_or view_name des Ziels mit <table_source> verknüpft werden kann, um die zu entfernenden Zeilen zu identifizieren. Diese Transact-SQL-Erweiterung für DELETE kann anstelle einer Unterabfrage in der WHERE-Klausel verwendet werden.

  Weitere Informationen finden Sie unter FROM (Transact-SQL).

• WHERE
  Gibt die Bedingungen an, die zur Beschränkung der Anzahl der zu löschenden Zeilen verwendet werden. Wird keine WHERE-Klausel angegeben, werden mit DELETE alle Zeilen aus der Tabelle entfernt.

  Es gibt zwei Formen von Löschoperationen, die darauf basieren, was in der WHERE-Klausel angegeben wird:

  • Gesuchte Löschungen geben eine Suchbedingung an, um die zu löschenden Zeilen zu kennzeichnen. Beispiel: WHERE column_name = value.

  • Positionierte Löschungen verwenden die CURRENT OF-Klausel, um einen Cursor anzugeben. Die Löschoperation wird an der aktuellen Position des Cursors ausgeführt. Dies kann genauer sein als eine gesuchte DELETE-Anweisung, die eine WHERE search_condition-Klausel zur Qualifizierung der zu löschenden Zeilen verwendet. Eine gesuchte DELETE-Anweisung löscht mehrere Zeilen, wenn die Suchbedingung nicht eindeutig eine einzelne Zeile identifiziert.

• search_condition
  Gibt die Einschränkungsbedingungen für die zu löschenden Zeilen an. Es gibt keinen Höchstwert hinsichtlich der Anzahl der Prädikate in einer Suchbedingung. Weitere Informationen finden Sie unter Suchbedingung (Transact-SQL).

• CURRENT OF
  Gibt an, dass DELETE an der aktuellen Position des angegebenen Cursors durchgeführt wird.

• GLOBAL
  Gibt an, dass cursor_name auf einen globalen Cursor verweist.

• cursor_name
  Der Name des geöffneten Cursors, von dem der Abruf erfolgt. Wenn sowohl ein globaler als auch ein lokaler Cursor namens cursor_name vorhanden ist, bezieht sich dieses Argument auf den globalen Cursor, wenn GLOBAL angegeben ist. Andernfalls bezieht es sich auf den lokalen Cursor. Der Cursor muss Aktualisierungen zulassen.

• cursor_variable_name
  Der Name einer Cursorvariablen. Die Cursorvariable muss auf einen Cursor verweisen, der Aktualisierungen zulässt.

• OPTION (query_hint [ ,... n] )
  Schlüsselwörter, die angeben, dass Hinweise für den Optimierer verwendet werden, um die Verarbeitung der Anweisung durch Database Engine (Datenbankmodul) anzupassen. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).

Bewährte Methoden

Verwenden Sie TRUNCATE TABLE, um alle Zeilen in einer Tabelle zu löschen. TRUNCATE TABLE ist schneller als DELETE und verwendet weniger System- und Transaktionsprotokollierungsressourcen.

Verwenden Sie die @@ROWCOUNT-Funktion, um die Anzahl der gelöschten Zeilen an die Clientanwendung zurückzugeben. Weitere Informationen finden Sie unter @@ROWCOUNT (Transact-SQL).

Kompatibilitätsunterstützung

Das Verwenden von SET ROWCOUNT wird in der nächsten Version von SQL Server keine Auswirkungen auf DELETE-Anweisungen haben. Verwenden Sie SET ROWCOUNT bei neuen Entwicklungsarbeiten nicht zusammen mit DELETE-Anweisungen, und ändern Sie Anwendungen, die SET ROWCOUNT derzeit verwenden, sodass sie die TOP-Syntax verwenden.

Fehlerbehandlung

Sie können die Fehlerbehandlung für die DELETE-Anweisung durch Angeben der Anweisung in einem TRY…CATCH-Konstrukt implementieren. Weitere Informationen finden Sie unter Verwenden von TRY...CATCH in Transact-SQL.

Die DELETE-Anweisung erzeugt möglicherweise einen Fehler, wenn sie gegen einen Trigger verstößt oder versucht, eine Zeile zu entfernen, auf die von Daten einer anderen Tabelle mit einer FOREIGN KEY-Einschränkung verwiesen wird. Entfernt die DELETE-Anweisung mehrere Zeilen und verstößt eine der entfernten Zeilen gegen einen Trigger oder eine Einschränkung, wird die Anweisung abgebrochen, ein Fehler gemeldet und keine Zeilen entfernt.

Wenn in einer DELETE-Anweisung bei der Auswertung eines Ausdrucks ein arithmetischer Fehler (Überlauf, Division durch null oder Domänenfehler) auftritt, behandelt Database Engine (Datenbankmodul) den Fehler so, als wäre SET ARITHABORT auf ON festgelegt. Der Rest des Batches wird abgebrochen, und eine Fehlermeldung wird zurückgegeben.

Interoperabilität

DELETE kann im Textkörper einer benutzerdefinierten Funktion verwendet werden, wenn es sich bei dem geänderten Objekt um eine Tabellenvariable handelt.

Wenn Sie eine Zeile mit einer FILESTREAM-Spalte löschen, löschen Sie auch die zugrunde liegenden Dateisystemdateien. Die zugrunde liegenden Dateien werden vom FILESTREAM Garbage Collector entfernt. Weitere Informationen finden Sie unter Verwalten von FILESTREAM-Daten mit Transact-SQL.

Die FROM-Klausel kann nicht für eine DELETE-Anweisung angegeben werden, die entweder direkt oder indirekt auf eine Sicht verweist, für die ein INSTEAD OF-Trigger definiert wurde. Weitere Informationen zu INSTEAD OF-Triggern finden Sie unter CREATE TRIGGER (Transact-SQL).

Einschränkungen

Wenn TOP mit DELETE verwendet wird, werden die Zeilen, auf die verwiesen wird, nicht in einer bestimmten Reihenfolge angeordnet, und die ORDER BY-KLausel kann in dieser Anweisung nicht direkt angegeben werden. Wenn Sie die TOP-Klausel verwenden müssen, um Zeilen in einer sinnvollen Reihenfolge zu löschen, müssen Sie sie zusammen mit einer ORDER BY-Klausel in einer untergeordneten SELECT-Anweisung verwenden. Informationen hierzu finden Sie im Abschnitt mit Beispielen nach diesem Thema.

TOP darf nicht in einer DELETE-Anweisung für partitionierte Sichten verwendet werden.

Die Einstellung der Option SET ROWCOUNT wird für DELETE-Anweisungen ignoriert, die für Remotetabellen oder für lokale oder verteilte partitionierte Sichten ausgeführt werden.

Sperrverhalten

Wenn Zeilen aus einem Heap gelöscht werden, können von Database Engine (Datenbankmodul) Zeilen- oder Seitensperren für den Vorgang verwendet werden. Demzufolge bleiben die durch den Löschvorgang geleerten Seiten dem Heap zugeordnet. Wenn die Zuordnung leerer Seiten nicht aufgehoben wird, kann der zugehörige Speicherplatz nicht für andere Objekte in der Datenbank verwendet werden. Verwenden Sie eine der folgenden Methoden, um Zeilen in einem Heap zu löschen und die Zuordnung der Seiten aufzuheben:

• Geben Sie den TABLOCK-Hinweis in der DELETE-Anweisung an. Mithilfe des TABLOCK-Hinweises wird durch den Löschvorgang eine freigegebene Sperre für die Tabelle statt einer Zeilen- oder Seitensperre angefordert. Dadurch ist es möglich, die Zuordnung der Seiten aufzuheben. Weitere Informationen zum TABLOCK-Hinweis finden Sie unter Tabellenhinweise (Transact-SQL).

• Verwenden Sie TRUNCATE TABLE, wenn alle Zeilen aus der Tabelle gelöscht werden sollen.

• Erstellen Sie einen gruppierten Index für den Heap, bevor Sie die Zeilen löschen. Nach dem Löschen der Zeilen können Sie den gruppierten Index löschen. Diese Methode ist zeitaufwändiger als die vorherigen Methoden und beansprucht mehr temporäre Ressourcen.

Weitere Informationen zu Sperren finden Sie unter Sperren im Datenbankmodul.

Protokollierungsverhalten

Die DELETE-Anweisung wird immer vollständig protokolliert.

Berechtigungen

Für die Zieltabelle sind DELETE-Berechtigungen erforderlich. SELECT-Berechtigungen werden ebenfalls benötigt, wenn die Anweisung eine WHERE-Klausel enthält.

DELETE-Berechtigungen werden standardmäßig Mitgliedern der festen Serverrolle sysadmin und der festen Datenbankrollen db_owner und db_datawriter sowie Tabellenbesitzern erteilt. Mitglieder der Rollen sysadmin, db_owner und db_securityadmin sowie der Tabellenbesitzer können Berechtigungen an andere Benutzer übertragen.

Beispiele

Grundlegende Syntax

Anhand von Beispielen in diesem Abschnitt wird die grundlegende Funktion der DELETE-Anweisung mithilfe der mindestens erforderlichen Syntax veranschaulicht.

Im folgenden Beispiel werden alle Zeilen aus der SalesPersonQuotaHistory-Tabelle gelöscht, da keine WHERE-Klausel verwendet wird, um die Anzahl der gelöschten Zeilen zu begrenzen.

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

Angeben der zu löschenden Zeilen

Die Beispiele in diesem Abschnitt zeigen Möglichkeiten, die Anzahl der gelöschten Zeilen zu begrenzen.

A. Verwenden der WHERE-Klausel zum Begrenzen von Zeilen

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

B. Verwenden der TOP-Klausel

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. Verwenden eines Cursors zum Löschen der aktuellen Zeile

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. Verwenden einer Unterabfrage und der Transact-SQL-FROM-Erweiterung

-- 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. Verwenden eines allgemeinen Tabellenausdrucks

Angeben von Zielobjekten, die keine Standardtabellen sind

In den Beispielen in diesem Abschnitt wird veranschaulicht, wie Zeilen durch Angeben einer Sicht oder Tabellenvariablen gelöscht werden.

Löschen von Zeilen in einer Remotetabelle

In den Beispielen in diesem Abschnitt wird das Löschen von Zeilen in einer Remotezieltabelle veranschaulicht, wobei mithilfe eines Verbindungsservers oder einer Rowsetfunktion auf die Remotetabelle verwiesen wird.

Außerkraftsetzen des Standardverhaltens des Abfrageoptimierers mithilfe von Hinweisen

In den Beispielen in diesem Abschnitt wird gezeigt, wie mit Tabellenhinweisen und Abfragehinweisen beim Verarbeiten der DELETE-Anweisung zeitweise das Standardverhalten des Abfrageoptimierers außer Kraft gesetzt wird.

Vorsicht
Da der SQL Server-Abfrageoptimierer in der Regel den optimalen Ausführungsplan für eine Abfrage auswählt, wird empfohlen, dass erfahrene Entwickler und Datenbankadministratoren Hinweise nur dann verwenden, wenn sich alle anderen Möglichkeiten als unzureichend erwiesen haben.

Im folgenden Beispiel wird der READPAST-Tabellenhinweis angegeben. Wenn READPAST angegeben ist, werden die Sperren auf Zeilen- und auf Seitenebene übersprungen. Dies führt dazu, dass das Datenbankmodul keine Zeilen und Seiten liest, die von anderen Transaktionen gesperrt sind. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

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

Erfassen der Ergebnisse der DELETE-Anweisung

In den Beispielen in diesem Abschnitt wird gezeigt, wie mit der OUTPUT-Klausel Informationen aus jeder von einer DELETE-Anweisung betroffenen Zeile bzw. Ausdrücke, die auf jeder Zeile basieren, zurückgegeben werden. Diese Ergebnisse können an die verarbeitende Anwendung zurückgegeben werden, die sie z. B. für Bestätigungen, Archivierungen und andere Anwendungsanforderungen verwendet.

A. Verwenden von DELETE mit der OUTPUT-Klausel

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. Verwenden von OUTPUT mit from_table_name in einer DELETE-Anweisung

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

Siehe auch

Verweis

Konzepte