De bästa sätten att använda SQL Delete Statement i en SQL-tabell

I relationsdatabaser skapar vi tabeller för att lagra data i olika format. SQL Server lagrar data i ett rad- och kolumnformat som innehåller ett värde kopplat till varje datatyp. När vi utformar SQL-tabeller definierar vi datatyper som t.ex. heltal, float, decimal, varchar och bit. En tabell som lagrar kunddata kan till exempel ha fält som kundnamn, e-post, adress, stat, land och så vidare. Olika SQL-kommandon utförs på en SQL-tabell och kan delas in i följande kategorier:

• Data Definition Language (DDL): Dessa kommandon används för att skapa och ändra databasobjekten i en databas.
  • Skapa: Skapa objekt
  • Alter: Ändrar objekt
  • Drop: Tar bort objekt
  • Truncate: Tar bort alla data från en tabell
• Data Manipulation Language (DML): Dessa kommandon infogar, hämtar, ändrar, raderar och uppdaterar data i databasen.
  • Välj: Hämtar data från en eller flera tabeller
  • Infoga: Lägg till nya data i en tabell
  • Uppdatera: Ändrar befintliga data
  • Ta bort: Tar bort befintliga poster i en tabell

• Data Control Language (DCL): Dessa kommandon är kopplade till rättighets- eller behörighetskontroller i en databas.
  • Grant: Denna kommandon är kopplade till rättigheter eller behörighetskontroller i en databas: Tilldelar behörigheter till en användare
  • Återkallar: Tilldelar behörigheter till en användare: Återkallar behörigheter från en användare
• Transaction Control Language (TCL): Dessa kommandon styr transaktioner i en databas.
  • Commit: Spara de ändringar som gjorts av frågan
  • Rollback: Rollback: Rullar tillbaka en explicit eller implicit transaktion till transaktionens början eller till en sparpunkt i transaktionen
  • Spara transaktioner: Rullar tillbaka de ändringar som gjorts i samband med en sökning: Sätter en sparpunkt eller markör i en transaktion

Antag att du har kundorderdata lagrade i en SQL-tabell. Om du fortsätter att lägga in data i den här tabellen kontinuerligt kan tabellen innehålla miljontals poster, vilket skulle orsaka prestandaproblem i dina program. Ditt indexunderhåll skulle också kunna bli extremt tidskrävande. Ofta behöver du inte behålla beställningar som är äldre än tre år. I dessa fall kan du ta bort dessa poster från tabellen. Detta skulle spara lagringsutrymme och minska ditt underhållsarbete.

Du kan ta bort data från en SQL-tabell på två sätt:

• Med hjälp av ett SQL delete-kommando
• Med hjälp av truncate

Vi kommer att titta på skillnaden mellan dessa SQL-kommandon senare. Låt oss först utforska SQL delete-kommandot.

Ett SQL delete-kommando utan villkor

I DML-kommandon (data manipulation language) tar ett SQL delete-kommando bort rader från en tabell. Du kan ta bort en specifik rad eller alla rader. Ett grundläggande delete-uttalande kräver inga argument.

Låt oss skapa en Orders SQL-tabell med hjälp av nedanstående skript. Den här tabellen har tre kolumner , och .

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

Insätt några poster i den här tabellen.

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

Antag nu att vi vill radera tabellens data. Du kan ange tabellens namn för att ta bort data med hjälp av delete-anvisningen. Båda SQL-anvisningarna är likadana. Vi kan ange tabellnamnet från nyckelordet (valfritt) eller ange tabellnamnet direkt efter delete.

Delete Orders Go Delete from Orders GO

En SQL delete-anvisning med filtrerade data

Dessa SQL delete-anvisningar tar bort alla tabellens data. Vanligtvis tar vi inte bort alla rader från en SQL-tabell. För att ta bort en specifik rad kan vi lägga till en where-klausul med delete-anvisningen. Where-klausulen innehåller filterkriterierna och bestämmer slutligen vilken eller vilka rader som ska tas bort.

Antag till exempel att vi vill ta bort order-id 1. När vi lägger till en where-klausul kontrollerar SQL Server först motsvarande rader och tar bort de specifika raderna.

Delete Orders where orderid=1

Om where-klausulens villkor är falskt tas inga rader bort. Vi har till exempel tagit bort den beställda 1 från tabellen orders. Om vi utför uttalandet igen hittar det inga rader som uppfyller where-klausulens villkor. I det här fallet returneras 0 berörda rader.

SQL delete statement och TOP clause

Du kan använda TOP statement för att ta bort raderna också. Nedanstående fråga tar till exempel bort de 100 bästa raderna från tabellen Orders.

Delete top (100)  from Orders

Då vi inte har angett någon ”ORDER BY” väljer den slumpmässiga rader och tar bort dem. Vi kan använda klausulen Order by för att sortera data och ta bort de översta raderna. I nedanstående fråga sorterar den i fallande ordning och tar sedan bort den från tabellen.

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

Raderar rader baserade på en annan tabell

Ibland behöver vi radera rader baserade på en annan tabell. Denna tabell kan finnas i samma databas eller inte.

• Table lookup

Vi kan använda metoden table lookup eller SQL join för att radera dessa rader. Vi vill till exempel ta bort rader från tabellen som uppfyller följande villkor:

Det ska finnas motsvarande rader i . tabellen.

Se på nedanstående fråga, här har vi ett select-uttalande i where-klausulen i delete-uttalandet. SQL Server hämtar först de rader som uppfyller select-anvisningen och tar sedan bort dessa rader från tabellen med hjälp av SQL delete-anvisningen.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

Alternativt kan vi använda SQL joins mellan dessa tabeller och ta bort raderna. I nedanstående fråga sammanfogar vi tabellerna ] med tabellen. En SQL-hopning fungerar alltid på en gemensam kolumn mellan tabellerna. Vi har en kolumn som förenar de båda tabellerna tillsammans.

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

För att förstå ovanstående delete-anvisning ska vi se den faktiska exekveringsplanen.

Enligt exekveringsplanen utförs en tabellsökning på båda tabellerna, hämtar matchande data och raderar dem från tabellen Orders.

• Gemensamt tabelluttryck (CTE)

Vi kan använda ett gemensamt tabelluttryck (CTE) för att ta bort rader från en SQL-tabell också. Först definierar vi en CTE för att hitta raden som vi vill ta bort.

Därefter kopplar vi ihop CTE:n med SQL-tabellen Orders och tar bort raderna.

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;

Inverkan på identitetsintervallet

Identitetskolumner i SQL Server genererar unika, sekventiella värden för din kolumn. De används främst för att unikt identifiera en rad i SQL-tabellen. En primärnyckelkolumn är också ett bra val för ett klusterindex i SQL Server.

I nedanstående skript har vi en tabell. Denna tabell har en identitetskolumn id.

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

Vi infogade 50 poster i denna tabell vilket genererade identitetsvärdena för kolumnen id.

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

Om vi raderar några rader från SQL-tabellen återställs inte identitetsvärdena för de efterföljande värdena. Låt oss till exempel ta bort några rader som har identitetsvärdena 20 till 25.

Delete from employee where id between 20 and 25

Visa nu tabellens poster.

Select * from employee where id>15

Det visar gapet i intervallet för identitetsvärdena.

SQL delete-anvisningen och transaktionsloggen

SQL delete loggar varje rads borttagning i transaktionsloggen. Anta att du behöver ta bort miljontals poster från en SQL-tabell. Du vill inte radera ett stort antal poster i en enda transaktion eftersom det kan leda till att loggfilen växer exponentiellt och att din databas också kan bli otillgänglig. Om du avbryter en transaktion mitt i kan det ta flera timmar att återkalla ett raderingsmeddelande.

I det här fallet bör du alltid radera rader i små bitar och bekräfta dessa bitar regelbundet. Du kan till exempel ta bort ett parti med 10 000 rader åt gången, bekräfta det och gå vidare till nästa parti. När SQL Server bekräftar biten kan transaktionsloggtillväxten kontrolleras.

Bästa praxis

• Du bör alltid utföra en säkerhetskopia innan du tar bort data.
• SQL Server använder som standard implicita transaktioner och bekräftar posterna utan att fråga användaren. Som bästa praxis bör du starta en explicit transaktion med Begin Transaction. Det ger dig kontroll över om du vill bekräfta eller återkalla transaktionen. Du bör också köra frekventa säkerhetskopior av transaktionsloggen om databasen är i fullständigt återställningsläge.
• Du vill ta bort data i små bitar för att undvika överdriven användning av transaktionsloggen. Det undviker också blockeringar även för andra SQL-transaktioner.
• Du bör begränsa behörigheter så att användare inte kan ta bort data. Endast behöriga användare ska ha tillgång till att ta bort data från en SQL-tabell.
• Du vill köra delete-angivelsen med en where-klausul. Den tar bort filtrerade data från en SQL-tabell. Om din applikation kräver frekvent borttagning av data är det en bra idé att återställa identitetsvärdena med jämna mellanrum. Annars kan du få problem med uttömmande identitetsvärden.
• Om du vill tömma tabellen är det lämpligt att använda truncate-anvisningen. Truncate-anvisningen tar bort alla data från en tabell, använder minimal transaktionsloggning, återställer identitetsvärdesintervallet och är snabbare än SQL delete-anvisningen eftersom den avallokerar alla sidor för tabellen omedelbart.
• Om du använder Foreign Key Constraints (förälder-barn-relation) för dina tabeller bör du ta bort raden från en underordnad rad och sedan från den överordnade tabellen. Om du tar bort raden från den överordnade raden kan du också använda alternativet Kaskad vid borttagning för att automatiskt ta bort raden från en underordnad tabell. Du kan läsa artikeln: Om du använder top-angivelsen för att ta bort raderna raderar SQL Server raderar raderna slumpmässigt. Du bör alltid använda top-klausulen med motsvarande Order by- och Group by-klausul.
• Ett delete-uttalande förvärvar en exklusiv avsiktslåsning på referenstabellen; under den tiden kan därför inga andra transaktioner ändra data. Du kan använda NOLOCK-hänvisningen för att läsa data.
• Du bör undvika att använda table hint för att åsidosätta standardlåsningsbeteendet för SQL delete statement; den bör endast användas av erfarna DBA:er och utvecklare.

Väsentliga överväganden

Det finns många fördelar med att använda SQL delete statements för att ta bort data från en SQL-tabell, men som du kan se kräver det ett metodiskt tillvägagångssätt. Det är viktigt att alltid ta bort data i små partier och att gå försiktigt fram när man tar bort data från en produktionsinstans. Att ha en säkerhetskopieringsstrategi för att återställa data på minsta möjliga tid är ett måste för att undvika driftstopp eller framtida prestandapåverkan.