De bedste måder at bruge SQL Delete Statement i en SQL-tabel

I relationelle databaser opretter vi tabeller til at gemme data i forskellige formater. SQL Server gemmer data i et række- og kolonneformat, som indeholder en værdi, der er knyttet til hver datatype. Når vi designer SQL-tabeller, definerer vi datatyper som f.eks. heltal, float, decimal, varchar og bit. En tabel, der gemmer kundedata, kan f.eks. have felter som kundenavn, e-mail, adresse, stat, land osv. Der udføres forskellige SQL-kommandoer på en SQL-tabel, og de kan opdeles i følgende kategorier:

• Data Definition Language (DDL): Disse kommandoer bruges til at oprette og ændre databaseobjekter i en database.
  • Opret: Opret objekter
  • Ændre: Opretter objekter: Ændrer objekter
  • Drop: Sletter objekter
  • Truncate: Sletter alle data fra en tabel
• Data Manipulation Language (DML): Sletter alle data fra en tabel

• Data Control Language (DCL): Data Control Language (DCL): Disse kommandoer er forbundet med kontrol af rettigheder eller tilladelser i en database.
  • Grant: Tildeler tilladelser til en bruger
  • Tilbagekaldes: Tilbagekalder tilladelser fra en bruger
• Transaction Control Language (TCL): Disse kommandoer styrer transaktioner i en database.
  • Commit: Gemmer de ændringer, der er foretaget af forespørgslen
  • Rollback: Rollback: Ruller en eksplicit eller implicit transaktion tilbage til begyndelsen af transaktionen eller til et savepoint inden for transaktionen
  • Gem transaktioner: Indstiller et savepoint eller en markering inden for en transaktion

Sæt, at du har kundeordredata gemt i en SQL-tabelle. Hvis du blev ved med at indsætte data i denne tabel løbende, kunne tabellen indeholde millioner af poster, hvilket ville give problemer med ydeevnen i dine programmer. Din indeksvedligeholdelse kunne også blive ekstremt tidskrævende. Ofte har du ikke brug for at opbevare ordrer, der er ældre end tre år. I disse tilfælde kan du slette disse poster fra tabellen. Dette ville spare lagerplads og reducere din vedligeholdelsesindsats.

Du kan fjerne data fra en SQL-tabelle på to måder:

• Ved hjælp af en SQL delete-anvisning
• Ved hjælp af en truncate

Vi vil se på forskellen mellem disse SQL-kommandoer senere. Lad os først undersøge SQL-sletteanvisningen.

En SQL-sletteanvisning uden betingelser

I DML-anvisninger (data manipulation language) fjerner en SQL-sletteanvisning rækkerne fra en tabel. Du kan slette en bestemt række eller alle rækker. En grundlæggende delete-anvisning kræver ikke nogen argumenter.

Lad os oprette en SQL-tabelle Orders ved hjælp af nedenstående script. Denne tabel har tre kolonner , og .

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

Indsæt et par poster i denne tabel.

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

Nu antager vi, at vi ønsker at slette tabellens data. Du kan angive tabellens navn for at fjerne data ved hjælp af delete-anvisningen. Begge SQL-angivelser er de samme. Vi kan angive tabelnavnet fra nøgleordet (valgfrit) eller angive tabelnavnet direkte efter delete.

Delete Orders Go Delete from Orders GO

En SQL-sletteanvisning med filtrerede data

Disse SQL-sletteanvisninger sletter alle tabellens data. Normalt fjerner vi ikke alle rækker fra en SQL-tabel. Hvis vi vil fjerne en bestemt række, kan vi tilføje en where-klausul med delete-erklæringen. Where-klausulen indeholder filterkriterierne og bestemmer i sidste ende, hvilke(n) række(r) der skal fjernes.

For eksempel antager vi, at vi ønsker at fjerne ordre-id 1. Når vi tilføjer en where-klausul, kontrollerer SQL Server først de tilsvarende rækker og fjerner disse specifikke rækker.

Delete Orders where orderid=1

Hvis where-klausulens betingelse er falsk, fjernes der ikke nogen rækker. Vi har f.eks. fjernet den bestilte 1 fra tabellen orders. Hvis vi udfører erklæringen igen, finder den ikke nogen rækker, der opfylder where-klausulens betingelse. I dette tilfælde returnerer den 0 berørte rækker.

SQL-sletteanvisning og TOP-klausul

Du kan også bruge TOP-anvisningen til at slette rækkerne. Nedenstående forespørgsel sletter f.eks. de 100 øverste rækker fra tabellen Ordrer.

Delete top (100)  from Orders

Da vi ikke har angivet nogen ‘ORDER BY’, vælger den tilfældige rækker og sletter dem. Vi kan bruge “Order by”-klausulen til at sortere dataene og slette de øverste rækker. I nedenstående forespørgsel sorterer den i faldende rækkefølge og sletter den derefter fra tabellen.

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

Sletning af rækker baseret på en anden tabel

I nogle tilfælde har vi brug for at slette rækker baseret på en anden tabel. Denne tabel findes måske i den samme database eller ej.

• Table lookup

Vi kan bruge table lookup-metoden eller SQL join til at slette disse rækker. Vi ønsker f.eks. at slette rækker fra tabellen, der opfylder følgende betingelse:

Det skal have tilsvarende rækker i . tabellen.

Se på nedenstående forespørgsel, her har vi en select-anvisning i where-klausulen i delete-anvisningen. SQL Server henter først de rækker, der opfylder select-anvisningen, og fjerner derefter disse rækker fra tabellen ved hjælp af SQL delete-anvisningen.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

Alternativt kan vi bruge SQL-joins mellem disse tabeller og fjerne rækkerne. I nedenstående forespørgsel sammenføjer vi tabellerne ] med tabellen. Et SQL-joint fungerer altid på en fælles kolonne mellem tabellerne. Vi har en kolonne, der føjer begge tabeller sammen.

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

For at forstå ovenstående sletteanvisning skal vi se den faktiske udførelsesplan.

Som i udførelsesplanen udføres der en bordscanning på begge tabeller, får de matchende data og sletter dem fra tabellen Ordrer.

• Common table expression (CTE)

Vi kan også bruge et common table expression (CTE) til at slette rækker fra et SQL-tabeller. Først definerer vi en CTE for at finde den række, vi vil fjerne.

Dernæst føjer vi CTE’en til SQL-tabellen Ordrer og sletter rækkerne.

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;

Indflydelse på identitetsområdet

Identitetsspalterne i SQL Server genererer unikke, sekventielle værdier for din kolonne. De bruges primært til at identificere en række i SQL-tabellen på en entydig måde. En primærnøglespalte er også et godt valg til et clusteret indeks i SQL Server.

I nedenstående script har vi en tabel. Denne tabel har en identitetskolonne id.

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

Vi indsatte 50 poster i denne tabel, hvilket genererede identitetsværdierne for id-kolonnen.

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

Hvis vi sletter et par rækker fra SQL-tabellen, nulstiller det ikke identitetsværdierne for de efterfølgende værdier. Lad os f.eks. slette nogle få rækker, der har identitetsværdierne 20 til 25.

Delete from employee where id between 20 and 25

Se nu tabellens poster.

Select * from employee where id>15

Det viser hullet i identitetsværdiintervallet.

SQL delete statement og transaktionsloggen

SQL delete logger hver række, der slettes, i transaktionsloggen. Antag, at du har brug for at slette millioner af poster fra en SQL-tabel. Du ønsker ikke at slette et stort antal poster i en enkelt transaktion, fordi det kan få logfilen til at vokse eksponentielt, og din database kan også være utilgængelig. Hvis du afbryder en transaktion midt i, kan det tage flere timer at rulle en sletningsanvisning tilbage.

I dette tilfælde bør du altid slette rækker i små bidder og regelmæssigt commit disse bidder. Du kan f.eks. slette en batch på 10.000 rækker ad gangen, bekræfte den og gå videre til den næste batch. Når SQL Server committerer chunken, kan væksten i transaktionsloggen styres.

Bedste praksis

• Du bør altid udføre en sikkerhedskopi, før du sletter data.
• Som standard bruger SQL Server implicitte transaktioner og committerer posterne uden at spørge brugeren. Som en bedste praksis bør du starte en eksplicit transaktion ved hjælp af Begin Transaction (Begynd transaktion). Det giver dig kontrol over at committe eller rollback transaktionen. Du bør også køre hyppige sikkerhedskopier af transaktionsloggen, hvis din database er i fuld genoprettelsestilstand.
• Du ønsker at slette data i små bidder for at undgå overdreven brug af transaktionsloggen. Det undgår også blokeringer for andre SQL-transaktioner.
• Du bør begrænse tilladelser, så brugere ikke kan slette data. Kun de autoriserede brugere bør have adgang til at fjerne data fra en SQL-tabel.
• Du ønsker at køre delete-anvisningen med en where-klausul. Den fjerner filtrerede data fra en SQL-tabel. Hvis dit program kræver hyppig sletning af data, er det en god idé at nulstille identitetsværdierne med jævne mellemrum. Ellers kan du få problemer med udtømning af identitetsværdier.
• Hvis du ønsker at tømme tabellen, er det tilrådeligt at bruge truncate-anvisningen. Truncate-erklæringen fjerner alle data fra en tabel, bruger minimal transaktionslogning, nulstiller identitetsværdiområdet og er hurtigere end SQL-slette-erklæringen, fordi den deallokerer alle siderne til tabellen med det samme.
• Hvis du bruger fremmednøglebegrænsninger (forældre-barn-relation) for dine tabeller, bør du slette rækken fra en underordnede række og derefter fra den overordnede tabel. Hvis du sletter rækken fra den overordnede række, kan du også bruge indstillingen kaskade ved sletning til automatisk at slette rækken fra en underordnede tabel. Du kan henvise til artiklen: Du kan læse følgende artikel: Delete cascade and update cascade in SQL Server foreign key for yderligere indsigt.
• Hvis du bruger top-erklæringen til at slette rækkerne, sletter SQL Server rækkerne tilfældigt. Du bør altid bruge top-klausulen med den tilsvarende Order by- og Group by-klausul.
• En delete-anvisning erhverver en eksklusiv intent-lås på referencetabellen; derfor kan ingen andre transaktioner ændre dataene i det pågældende tidsrum. Du kan bruge NOLOCK-hinvisningen til at læse dataene.
• Du bør undgå at bruge tabelhintet til at tilsidesætte standardlåseadfærden for SQL delete-erklæringen; det bør kun bruges af erfarne DBA’er og udviklere.

Vigtige overvejelser

Der er mange fordele ved at bruge SQL delete-erklæringer til at fjerne data fra en SQL-tabelle, men som du kan se, kræver det en metodisk tilgang. Det er vigtigt altid at slette data i små batches og at gå forsigtigt frem, når du sletter data fra en produktionsinstans. Det er et must at have en backup-strategi til at gendanne data på kortest mulig tid for at undgå nedetid eller fremtidige påvirkninger af ydeevnen.