Cele mai bune moduri de utilizare a instrucțiunii SQL Delete într-un tabel SQL

În bazele de date relaționale, creăm tabele pentru a stoca date în diferite formate. SQL Server stochează datele într-un format de rând și coloană care deține o valoare asociată cu fiecare tip de date. Când proiectăm tabele SQL, definim tipuri de date cum ar fi integer, float, decimal, varchar și bit. De exemplu, o tabelă care stochează date despre clienți poate avea câmpuri cum ar fi numele clientului, adresa de e-mail, adresa, statul, țara și așa mai departe. Diferite comenzi SQL sunt efectuate asupra unui tabel SQL și pot fi împărțite în următoarele categorii:

• Limbajul de definire a datelor (DDL): Aceste comenzi sunt utilizate pentru a crea și modifica obiectele unei baze de date.
  • Create: Creează obiecte
  • Alter: Modifică obiecte
  • Drop: Șterge obiecte
  • Truncate: Șterge toate datele dintr-un tabel
• Limbajul de manipulare a datelor (DML): Aceste comenzi inserează, recuperează, modifică, șterg și actualizează date în baza de date.
  • Select: Preia date dintr-un singur tabel sau din mai multe
  • Insert: Adaugă date noi într-un tabel
  • Update: Modifică datele existente
  • Delete: Șterge înregistrările existente într-un tabel

• Data Control Language (DCL): Aceste comenzi sunt asociate cu controlul drepturilor sau permisiunilor într-o bază de date.
  • Grant: Atribuie permisiuni unui utilizator
  • Revoke: Revoacă permisiunile unui utilizator
• Limbajul de control al tranzacțiilor (TCL): Aceste comenzi controlează tranzacțiile într-o bază de date.
  • Commit: Salvează modificările făcute de interogare
  • Rollback: Returnează o tranzacție explicită sau implicită la începutul tranzacției sau la un punct de salvare din cadrul tranzacției
  • Save transactions: Stabilește un punct de salvare sau un marker în cadrul unei tranzacții

Să presupunem că aveți date privind comenzile clienților stocate într-un tabel SQL. Dacă ați continua să inserați continuu date în acest tabel, tabelul ar putea conține milioane de înregistrări, ceea ce ar cauza probleme de performanță în cadrul aplicațiilor dumneavoastră. De asemenea, întreținerea indexului dvs. ar putea deveni extrem de consumatoare de timp. Adesea, nu aveți nevoie să păstrați comenzi mai vechi de trei ani. În aceste cazuri, ați putea șterge acele înregistrări din tabel. Acest lucru ar economisi spațiu de stocare, precum și v-ar reduce eforturile de întreținere.

Puteți elimina datele dintr-un tabel SQL în două moduri:

• Utilizând o instrucțiune SQL de ștergere
• Utilizând o truncare

Vom analiza diferența dintre aceste comenzi SQL mai târziu. Să explorăm mai întâi instrucțiunea SQL delete.

O instrucțiune SQL delete fără condiții

În instrucțiunile de limbaj de manipulare a datelor (DML), o instrucțiune SQL delete elimină rândurile dintr-un tabel. Puteți șterge un anumit rând sau toate rândurile. O instrucțiune de ștergere de bază nu necesită niciun argument.

Să creăm un tabel SQL Orders folosind scriptul de mai jos. Acest tabel are trei coloane , și .

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

Inserați câteva înregistrări în acest tabel.

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

Acum, să presupunem că dorim să ștergem datele din tabel. Puteți specifica numele tabelului pentru a elimina datele folosind instrucțiunea delete. Ambele instrucțiuni SQL sunt identice. Putem specifica numele tabelului de la cuvântul cheie (opțional) sau putem specifica numele tabelului direct după eliminarea.

Delete Orders Go Delete from Orders GO

O instrucțiune SQL de eliminare cu date filtrate

Aceste instrucțiuni SQL de eliminare elimină toate datele din tabel. De obicei, nu eliminăm toate rândurile dintr-o tabelă SQL. Pentru a elimina un anumit rând, putem adăuga o clauză where cu instrucțiunea de ștergere. Clauza where conține criteriile de filtrare și, în cele din urmă, determină ce rând (rânduri) se elimină.

De exemplu, să presupunem că dorim să eliminăm comanda id 1. Odată ce adăugăm o clauză where, SQL Server verifică mai întâi rândurile corespunzătoare și elimină acele rânduri specifice.

Delete Orders where orderid=1

Dacă condiția clauzei where este falsă, nu elimină niciun rând. De exemplu, am eliminat ordinul 1 din tabelul comenzi. Dacă executăm din nou instrucțiunea, aceasta nu găsește niciun rând care să satisfacă condiția clauzei where. În acest caz, se returnează 0 rânduri afectate.

Explicația de ștergere SQL și clauza TOP

Puteți utiliza instrucțiunea TOP și pentru a șterge rândurile. De exemplu, interogarea de mai jos șterge primele 100 de rânduri din tabelul Orders.

Delete top (100)  from Orders

Din moment ce nu am specificat niciun ‘ORDER BY’, se aleg rânduri aleatorii și se șterg. Putem utiliza clauza Order by pentru a sorta datele și a șterge primele rânduri. În interogarea de mai jos, se ordonează în ordine descrescătoare și apoi se șterge din tabel.

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

Ștergerea rândurilor bazate pe un alt tabel

Câteodată trebuie să ștergem rânduri bazate pe un alt tabel. Această tabelă poate exista sau nu în aceeași bază de date.

• Table lookup

Potem folosi metoda de căutare a tabelelor sau SQL join pentru a șterge aceste rânduri. De exemplu, dorim să ștergem rândurile din tabel care îndeplinesc următoarea condiție:

Ar trebui să aibă rânduri corespunzătoare în tabelul ..

Consultați interogarea de mai jos, aici avem o instrucțiune select în clauza where a instrucțiunii de ștergere. SQL Server obține mai întâi rândurile care satisfac instrucțiunea select și apoi elimină acele rânduri din tabel folosind instrucțiunea SQL delete.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

Alternativ, putem folosi SQL joins între aceste tabele și să eliminăm rândurile. În interogarea de mai jos, alăturăm tabelele ] cu tabelul. O îmbinare SQL funcționează întotdeauna pe o coloană comună între tabele. Avem o coloană care unește ambele tabele.

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

Pentru a înțelege instrucțiunea de ștergere de mai sus, să vizualizăm planul de execuție real.

Potrivit planului de execuție, se efectuează o scanare a tabelelor pe ambele tabele, se obțin datele corespunzătoare și se șterg din tabelul Orders.

• Expresie comună de tabel (CTE)

Potem utiliza o expresie comună de tabel (CTE) și pentru a șterge rândurile dintr-un tabel SQL. Mai întâi, definim un CTE pentru a găsi rândul pe care dorim să îl eliminăm.

Apoi, alăturăm CTE cu tabelul SQL Orders și eliminăm rândurile.

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;

Impacte asupra intervalului de identitate

Colonii de identitate din SQL Server generează valori unice, secvențiale pentru coloana dumneavoastră. Ele sunt utilizate în principal pentru a identifica în mod unic un rând în tabelul SQL. O coloană cheie primară este, de asemenea, o alegere bună pentru un index clusterizat în SQL Server.

În scriptul de mai jos, avem o tabelă. Acest tabel are o coloană de identitate id.

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

Am inserat 50 de înregistrări în acest tabel care au generat valorile de identitate pentru coloana id.

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

Dacă ștergem câteva rânduri din tabelul SQL, acesta nu resetează valorile de identitate pentru valorile ulterioare. De exemplu, să ștergem câteva rânduri care au valorile de identitate de la 20 la 25.

Delete from employee where id between 20 and 25

Acum, vizualizați înregistrările din tabel.

Select * from employee where id>15

Se arată decalajul din intervalul valorilor de identitate.

Explicația de ștergere SQL și jurnalul de tranzacții

SQL delete înregistrează fiecare ștergere de rând în jurnalul de tranzacții. Să presupunem că trebuie să ștergeți milioane de înregistrări dintr-un tabel SQL. Nu doriți să ștergeți un număr mare de înregistrări într-o singură tranzacție, deoarece acest lucru ar putea face ca fișierul jurnal să crească exponențial și, de asemenea, baza de date ar putea fi indisponibilă. Dacă anulați o tranzacție la mijloc, ar putea dura ore întregi pentru a anula o instrucțiune de ștergere.

În acest caz, ar trebui să ștergeți întotdeauna rândurile în bucăți mici și să confirmați aceste bucăți în mod regulat. De exemplu, puteți șterge un lot de 10.000 de rânduri la un moment dat, să îl confirmați și să treceți la următorul lot. Atunci când SQL Server validează bucata, creșterea jurnalului de tranzacții poate fi controlată.

Cele mai bune practici

• Ar trebui să efectuați întotdeauna o copie de rezervă înainte de a șterge date.
• În mod implicit, SQL Server utilizează tranzacții implicite și validează înregistrările fără a întreba utilizatorul. Ca o bună practică, ar trebui să începeți o tranzacție explicită folosind Begin Transaction. Aceasta vă oferă controlul pentru a confirma sau a anula tranzacția. De asemenea, ar trebui să rulați frecvent copii de rezervă ale jurnalului de tranzacții dacă baza de date este în modul de recuperare completă.
• Vreți să ștergeți datele în bucăți mici pentru a evita utilizarea excesivă a jurnalului de tranzacții. De asemenea, se evită blocajele și pentru alte tranzacții SQL.
• Ar trebui să restricționați permisiunile astfel încât utilizatorii să nu poată șterge date. Numai utilizatorii autorizați ar trebui să aibă acces pentru a șterge date dintr-un tabel SQL.
• Vreți să executați instrucțiunea de ștergere cu o clauză where. Aceasta elimină datele filtrate dintr-un tabel SQL. Dacă aplicația dvs. necesită ștergerea frecventă a datelor, este o idee bună să resetați periodic valorile de identitate. În caz contrar, s-ar putea să vă confruntați cu probleme de epuizare a valorilor de identitate.
• În cazul în care doriți să goliți tabelul, este recomandabil să utilizați instrucțiunea truncate. Instrucțiunea truncate elimină toate datele dintr-un tabel, utilizează o logare minimă a tranzacțiilor, resetează intervalul valorilor de identitate și este mai rapidă decât instrucțiunea SQL delete, deoarece dezalocă imediat toate paginile pentru tabel.
• În cazul în care utilizați constrângeri de chei străine (relație părinte-copil) pentru tabelele dumneavoastră, ar trebui să ștergeți rândul dintr-un rând copil și apoi din tabelul părinte. Dacă ștergeți rândul din rândul părinte, puteți utiliza, de asemenea, opțiunea cascade on delete pentru a șterge automat rândul dintr-un tabel copil. Puteți consulta articolul: Ștergerea în cascadă și actualizarea în cascadă în SQL Server foreign key pentru mai multe informații.
• Dacă utilizați instrucțiunea top pentru a șterge rândurile, SQL Server șterge rândurile în mod aleatoriu. Ar trebui să utilizați întotdeauna clauza top cu clauza Order by și Group by corespunzătoare.
• O instrucțiune de ștergere dobândește un blocaj exclusiv de intenție asupra tabelului de referință; prin urmare, în acest timp, nicio altă tranzacție nu poate modifica datele. Puteți utiliza indicația NOLOCK pentru a citi datele.
• Ar trebui să evitați utilizarea indicelui de tabel pentru a suprascrie comportamentul de blocare implicit al instrucțiunii SQL delete; acesta ar trebui utilizat numai de către DBA și dezvoltatori experimentați.

Considerații importante

Există multe beneficii ale utilizării instrucțiunilor SQL delete pentru a elimina date dintr-un tabel SQL, dar, după cum puteți vedea, necesită o abordare metodică. Este important să ștergeți întotdeauna datele în loturi mici și să procedați cu prudență atunci când ștergeți date dintr-o instanță de producție. A avea o strategie de backup pentru a recupera datele în cel mai scurt timp este o necesitate pentru a evita timpii de nefuncționare sau impacturile viitoare asupra performanței.