Die besten Verwendungsmöglichkeiten für die SQL-Löschanweisung in einer SQL-Tabelle

In relationalen Datenbanken erstellen wir Tabellen, um Daten in verschiedenen Formaten zu speichern. SQL Server speichert Daten in einem Zeilen- und Spaltenformat, das für jeden Datentyp einen Wert enthält. Beim Entwurf von SQL-Tabellen werden Datentypen wie Integer, Float, Decimal, Varchar und Bit definiert. Eine Tabelle, die Kundendaten speichert, könnte beispielsweise Felder wie Kundenname, E-Mail, Adresse, Bundesland, Land usw. enthalten. Für eine SQL-Tabelle werden verschiedene SQL-Befehle ausgeführt, die sich in die folgenden Kategorien unterteilen lassen:

• Data Definition Language (DDL): Diese Befehle werden zum Erstellen und Ändern von Datenbankobjekten in einer Datenbank verwendet.
  • Erstellen: Erzeugt Objekte
  • Ändern: Modifiziert Objekte
  • Drop: Löscht Objekte
  • Truncate: Löscht alle Daten aus einer Tabelle
• Data Manipulation Language (DML): Diese Befehle dienen zum Einfügen, Abrufen, Ändern, Löschen und Aktualisieren von Daten in der Datenbank.
  • Auswählen: Ruft Daten aus einer einzelnen oder mehreren Tabellen ab
  • Einfügen: Fügt neue Daten in einer Tabelle hinzu
  • Aktualisieren: Ändert vorhandene Daten
  • Löschen: Löscht vorhandene Datensätze in einer Tabelle

• Data Control Language (DCL): Diese Befehle sind mit Rechte- oder Berechtigungssteuerungen in einer Datenbank verbunden.
  • Erteilen: Weist einem Benutzer Berechtigungen zu
  • Widerrufen: Entzieht einem Benutzer die Berechtigungen
• Transaction Control Language (TCL): Diese Befehle steuern Transaktionen in einer Datenbank.
  • Commit: Speichert die von der Abfrage vorgenommenen Änderungen
  • Rollback: Rollt eine explizite oder implizite Transaktion bis zum Beginn der Transaktion oder zu einem Savepoint innerhalb der Transaktion zurück
  • Save transactions: Setzt einen Speicherpunkt oder eine Markierung innerhalb einer Transaktion

Angenommen, Sie haben Kundenauftragsdaten in einer SQL-Tabelle gespeichert. Wenn Sie ständig Daten in diese Tabelle einfügen würden, könnte die Tabelle Millionen von Datensätzen enthalten, was zu Leistungsproblemen innerhalb Ihrer Anwendungen führen würde. Auch die Indexpflege könnte sehr zeitaufwändig werden. Oft müssen Sie Aufträge, die älter als drei Jahre sind, nicht aufbewahren. In diesen Fällen könnten Sie diese Datensätze aus der Tabelle löschen. Das spart Speicherplatz und reduziert den Wartungsaufwand.

Sie können Daten auf zwei Arten aus einer SQL-Tabelle entfernen:

• Mit einer SQL-Löschanweisung
• Mit einem Truncate-Befehl

Wir werden uns den Unterschied zwischen diesen SQL-Befehlen später ansehen. Betrachten wir zunächst die SQL-Löschanweisung.

Eine SQL-Löschanweisung ohne Bedingungen

In DML-Anweisungen (Data Manipulation Language) entfernt eine SQL-Löschanweisung die Zeilen aus einer Tabelle. Sie können eine bestimmte Zeile oder alle Zeilen löschen. Eine einfache Löschanweisung erfordert keine Argumente.

Lassen Sie uns eine SQL-Tabelle Orders mit dem folgenden Skript erstellen. Diese Tabelle hat drei Spalten, und.

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

Fügen Sie einige Datensätze in diese Tabelle ein.

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

Angenommen, wir wollen die Tabellendaten löschen. Sie können den Tabellennamen angeben, um die Daten mit der Delete-Anweisung zu löschen. Die beiden SQL-Anweisungen sind identisch. Wir können den Tabellennamen über das (optionale) Schlüsselwort angeben oder den Tabellennamen direkt nach dem delete angeben.

Delete Orders Go Delete from Orders GO

Eine SQL-Löschanweisung mit gefilterten Daten

Diese SQL-Löschanweisungen löschen alle Daten der Tabelle. Normalerweise werden nicht alle Zeilen aus einer SQL-Tabelle gelöscht. Um eine bestimmte Zeile zu entfernen, können wir der Löschanweisung eine Where-Klausel hinzufügen. Die Where-Klausel enthält die Filterkriterien und bestimmt schließlich, welche Zeile(n) entfernt werden soll(en).

Angenommen, wir möchten die Bestellnummer 1 entfernen. Sobald wir eine Where-Klausel hinzufügen, überprüft SQL Server zunächst die entsprechenden Zeilen und entfernt diese spezifischen Zeilen.

Delete Orders where orderid=1

Wenn die Bedingung der Where-Klausel falsch ist, werden keine Zeilen entfernt. Wir haben zum Beispiel die Reihenfolge 1 aus der Tabelle orders entfernt. Wenn wir die Anweisung erneut ausführen, findet sie keine Zeilen, die die Bedingung der Where-Klausel erfüllen. In diesem Fall werden 0 betroffene Zeilen zurückgegeben.

SQL-Löschanweisung und TOP-Klausel

Sie können die TOP-Anweisung auch zum Löschen der Zeilen verwenden. Zum Beispiel löscht die folgende Abfrage die obersten 100 Zeilen aus der Tabelle Orders.

Delete top (100)  from Orders

Da wir kein ‚ORDER BY‘ angegeben haben, werden zufällige Zeilen ausgewählt und gelöscht. Wir können die Order by-Klausel verwenden, um die Daten zu sortieren und die obersten Zeilen zu löschen. In der folgenden Abfrage werden die Daten in absteigender Reihenfolge sortiert und dann aus der Tabelle gelöscht.

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

Löschen von Zeilen basierend auf einer anderen Tabelle

Manchmal müssen wir Zeilen basierend auf einer anderen Tabelle löschen. Diese Tabelle kann in derselben Datenbank vorhanden sein oder auch nicht.

• Tabellennachschlag

Wir können die Methode des Tabellennachschlags oder des SQL-Join verwenden, um diese Zeilen zu löschen. Wir wollen zum Beispiel Zeilen aus der Tabelle löschen, die die folgende Bedingung erfüllen:

Sie sollten entsprechende Zeilen in der Tabelle . haben.

Schauen Sie sich die folgende Abfrage an, hier haben wir eine Select-Anweisung in der Where-Klausel der Delete-Anweisung. SQL Server ruft zunächst die Zeilen ab, die der select-Anweisung entsprechen, und entfernt diese Zeilen dann mit der SQL-Löschanweisung aus der Tabelle.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL-Join

Alternativ können wir SQL-Joins zwischen diesen Tabellen verwenden und die Zeilen entfernen. In der folgenden Abfrage verbinden wir die Tabellen ] mit der Tabelle. Ein SQL-Join arbeitet immer mit einer gemeinsamen Spalte zwischen den Tabellen. Wir haben eine Spalte, die beide Tabellen miteinander verbindet.

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

Um die obige Löschanweisung zu verstehen, sehen wir uns den tatsächlichen Ausführungsplan an.

Der Ausführungsplan führt einen Tabellenscan in beiden Tabellen durch, ermittelt die übereinstimmenden Daten und löscht sie aus der Tabelle „Orders“.

• Common Table Expression (CTE)

Wir können auch einen Common Table Expression (CTE) verwenden, um die Zeilen aus einer SQL-Tabelle zu löschen. Zuerst definieren wir eine CTE, um die Zeile zu finden, die wir entfernen wollen.

Dann verbinden wir die CTE mit den SQL-Tabellenaufträgen und löschen die Zeilen.

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;

Auswirkungen auf den Identitätsbereich

Identitätsspalten in SQL Server erzeugen eindeutige, aufeinander folgende Werte für Ihre Spalte. Sie werden in erster Linie zur eindeutigen Identifizierung einer Zeile in der SQL-Tabelle verwendet. Eine Primärschlüsselspalte ist auch eine gute Wahl für einen geclusterten Index in SQL Server.

Im folgenden Skript haben wir eine Tabelle. Diese Tabelle hat eine Identitätsspalte id.

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

Wir haben 50 Datensätze in diese Tabelle eingefügt, die die Identitätswerte für die id-Spalte generiert haben.

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

Wenn wir ein paar Zeilen aus der SQL-Tabelle löschen, werden die Identitätswerte für die nachfolgenden Werte nicht zurückgesetzt. Löschen wir zum Beispiel einige Zeilen mit den Identitätswerten 20 bis 25.

Delete from employee where id between 20 and 25

Sehen Sie sich nun die Tabellendatensätze an.

Select * from employee where id>15

Sie zeigen die Lücke im Identitätswertebereich.

SQL-Löschanweisung und das Transaktionsprotokoll

SQL-Löschen protokolliert jede Zeilenlöschung im Transaktionsprotokoll. Angenommen, Sie müssen Millionen von Datensätzen aus einer SQL-Tabelle löschen. Sie wollen nicht eine große Anzahl von Datensätzen in einer einzigen Transaktion löschen, da dies dazu führen könnte, dass Ihre Protokolldatei exponentiell wächst und Ihre Datenbank nicht mehr verfügbar ist. Wenn Sie eine Transaktion mittendrin abbrechen, kann es Stunden dauern, eine Löschanweisung rückgängig zu machen.

In diesem Fall sollten Sie Zeilen immer in kleinen Abschnitten löschen und diese Abschnitte regelmäßig übertragen. Sie können zum Beispiel einen Stapel von 10.000 Zeilen auf einmal löschen, diesen festschreiben und zum nächsten Stapel übergehen. Wenn SQL Server den Chunk festschreibt, kann das Wachstum des Transaktionsprotokolls kontrolliert werden.

Best Practices

• Sie sollten immer ein Backup durchführen, bevor Sie Daten löschen.
• Standardmäßig verwendet SQL Server implizite Transaktionen und schreibt die Datensätze fest, ohne den Benutzer zu fragen. Als Best Practice sollten Sie eine explizite Transaktion mit Begin Transaction starten. Dadurch haben Sie die Möglichkeit, die Transaktion zu bestätigen oder zurückzusetzen. Außerdem sollten Sie regelmäßig Sicherungen des Transaktionsprotokolls durchführen, wenn sich Ihre Datenbank im vollständigen Wiederherstellungsmodus befindet.
• Sie sollten Daten in kleinen Stücken löschen, um eine übermäßige Nutzung des Transaktionsprotokolls zu vermeiden. Dadurch werden auch Blockierungen für andere SQL-Transaktionen vermieden.
• Sie sollten die Berechtigungen einschränken, damit Benutzer keine Daten löschen können. Nur autorisierte Benutzer sollten Zugriff auf das Löschen von Daten aus einer SQL-Tabelle haben.
• Sie sollten die Delete-Anweisung mit einer Where-Klausel ausführen. Sie entfernt gefilterte Daten aus einer SQL-Tabelle. Wenn Ihre Anwendung häufiges Löschen von Daten erfordert, ist es eine gute Idee, die Identitätswerte regelmäßig zurückzusetzen. Andernfalls kann es zu Problemen mit der Erschöpfung der Identitätswerte kommen.
• Wenn Sie die Tabelle leeren wollen, ist es ratsam, die Anweisung truncate zu verwenden. Die truncate-Anweisung entfernt alle Daten aus einer Tabelle, verwendet eine minimale Transaktionsprotokollierung, setzt den Identitätswertbereich zurück und ist schneller als die SQL-Löschanweisung, da sie alle Seiten für die Tabelle sofort freigibt.
• Wenn Sie Fremdschlüssel-Beschränkungen (Eltern-Kind-Beziehung) für Ihre Tabellen verwenden, sollten Sie die Zeile aus einer untergeordneten Zeile und dann aus der übergeordneten Tabelle löschen. Wenn Sie die Zeile aus der übergeordneten Zeile löschen, können Sie auch die Option Kaskade beim Löschen verwenden, um die Zeile automatisch aus einer untergeordneten Tabelle zu löschen. Sie können sich auf den Artikel beziehen: Löschkaskade und Aktualisierungskaskade in SQL Server Fremdschlüssel für weitere Einblicke.
• Wenn Sie die top-Anweisung zum Löschen der Zeilen verwenden, löscht SQL Server die Zeilen zufällig. Sie sollten immer die Top-Klausel mit der entsprechenden Order by- und Group by-Klausel verwenden.
• Eine Löschanweisung erwirbt eine exklusive Sperre für die Referenztabelle; daher können während dieser Zeit keine anderen Transaktionen die Daten ändern. Sie können den NOLOCK-Hinweis verwenden, um die Daten zu lesen.
• Sie sollten es vermeiden, den Tabellenhinweis zu verwenden, um das standardmäßige Sperrverhalten der SQL-Löschanweisung außer Kraft zu setzen; er sollte nur von erfahrenen DBAs und Entwicklern verwendet werden.

Wichtige Überlegungen

Es gibt viele Vorteile der Verwendung von SQL-Löschanweisungen, um Daten aus einer SQL-Tabelle zu entfernen, aber wie Sie sehen können, erfordert es einen methodischen Ansatz. Es ist wichtig, Daten immer in kleinen Stapeln zu löschen und beim Löschen von Daten aus einer Produktionsinstanz mit Vorsicht vorzugehen. Eine Backup-Strategie zur Wiederherstellung von Daten in kürzester Zeit ist ein Muss, um Ausfallzeiten oder zukünftige Leistungseinbußen zu vermeiden.