Az SQL Delete utasítás használatának legjobb módjai egy SQL táblában

A relációs adatbázisokban táblákat hozunk létre különböző formátumú adatok tárolására. Az SQL Server az adatokat sor- és oszlopformátumban tárolja, amely az egyes adattípusokhoz tartozó értéket tárolja. Amikor SQL táblákat tervezünk, olyan adattípusokat határozunk meg, mint az integer, float, decimális, varchar és bit. Például egy ügyféladatokat tároló táblázatnak lehetnek olyan mezői, mint az ügyfél neve, e-mail címe, címe, állama, országa és így tovább. Egy SQL-táblán különböző SQL-parancsokat hajtunk végre, amelyek a következő kategóriákba sorolhatók:

• Adatdefiníciós nyelv (DDL): Ezek a parancsok az adatbázisban lévő adatbázis-objektumok létrehozására és módosítására szolgálnak.
  • Create: Objektumokat hoz létre
  • Módosít: Objektumok módosítása
  • Drop: Objektumok törlése
  • Truncate: Törli az összes adatot a táblázatból
• Adatmanipulációs nyelv (DML):
  • Select: Adatok kinyerése egy vagy több táblából
  • Beszúrás: Új adatok hozzáadása egy táblához
  • Frissítés: Meglévő adatok módosítása
  • Törlés: Meglévő rekordok törlése egy táblában

• Data Control Language (DCL):
  • Grant: Jogosultságok hozzárendelése egy felhasználóhoz
  • Visszavonás: Visszavonja a jogosultságokat egy felhasználótól
• Tranzakcióvezérlő nyelv (TCL):
  • Commit: Elmenti a lekérdezés által végrehajtott változtatásokat
  • Rollback: Visszapörget egy explicit vagy implicit tranzakciót a tranzakció elejére vagy a tranzakción belüli mentési pontra
  • Save transactions: Mentéspont vagy jelölés beállítása a tranzakción belül

Tegyük fel, hogy egy SQL-táblában tárolja a vásárlói megrendelések adatait. Ha folyamatosan adatokat szúrna be ebbe a táblába, a tábla több millió rekordot tartalmazhatna, ami teljesítményproblémákat okozna az alkalmazásokban. Az index karbantartása is rendkívül időigényessé válhatna. Gyakran előfordul, hogy nem kell megőriznie a három évnél régebbi megrendeléseket. Ilyenkor törölheti ezeket a rekordokat a táblából. Ezzel tárhelyet takaríthat meg, valamint csökkentheti a karbantartási erőfeszítéseit.

Egy SQL-táblából kétféleképpen távolíthat el adatokat:

• Egy SQL delete utasítás használatával
• Egy truncate

Az ezen SQL-parancsok közötti különbséget később fogjuk megvizsgálni. Először vizsgáljuk meg az SQL delete utasítást.

Az SQL delete utasítás feltételek nélkül

Az adatmanipulációs nyelv (DML) utasításaiban az SQL delete utasítás eltávolítja a sorokat egy táblázatból. Törölhet egy adott sort vagy az összes sort. Egy egyszerű törlési utasítás nem igényel semmilyen argumentumot.

Hozzunk létre egy Orders SQL-táblát az alábbi szkript segítségével. Ennek a táblának három oszlopa van , és .

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

Elhelyezzünk néhány rekordot ebbe a táblába.

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

Most tegyük fel, hogy törölni akarjuk a táblázat adatait. Az adatok törléséhez a delete utasítással megadhatjuk a tábla nevét. Mindkét SQL utasítás ugyanaz. Megadhatjuk a táblázat nevét a (választható) kulcsszóból, vagy közvetlenül a delete után adhatjuk meg a táblázat nevét.

Delete Orders Go Delete from Orders GO

Egy SQL delete utasítás szűrt adatokkal

Ezek az SQL delete utasítások törlik a táblázat összes adatát. Általában nem távolítjuk el az összes sort egy SQL-táblából. Egy adott sor eltávolításához a delete utasításhoz hozzáadhatunk egy where záradékot. A where záradék tartalmazza a szűrési feltételeket, és végül meghatározza, hogy melyik sor(ok) kerül(nek) eltávolításra.

Tegyük fel például, hogy a rendelési azonosítót 1 szeretnénk eltávolítani. Miután hozzáadtunk egy where záradékot, az SQL Server először ellenőrzi a megfelelő sorokat, és eltávolítja ezeket a konkrét sorokat.

Delete Orders where orderid=1

Ha a where záradék feltétele hamis, akkor nem távolít el egyetlen sort sem. Például a megrendelések táblából eltávolítottuk a rendezett 1-est. Ha újra végrehajtjuk az utasítást, nem talál olyan sort, amely megfelelne a where záradék feltételének. Ebben az esetben 0 érintett sort ad vissza.

SQL törlési utasítás és TOP záradék

A TOP utasítással is törölhetünk sorokat. Például az alábbi lekérdezés törli a legfelső 100 sort a Rendelések táblából.

Delete top (100)  from Orders

Mivel nem adtunk meg semmilyen ‘ORDER BY’-t, véletlenszerűen választja ki a sorokat és törli azokat. A Order by záradékot felhasználhatjuk az adatok rendezéséhez és a legfelső sorok törléséhez. Az alábbi lekérdezésben csökkenő sorrendbe rendezi, majd törli a táblázatból.

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

Sorok törlése egy másik táblázat alapján

Néha egy másik táblázat alapján kell sorokat törölnünk. Ez a tábla létezhet ugyanabban az adatbázisban vagy sem.

• Táblák keresése

Ezek a sorok törléséhez használhatjuk a táblák keresésének módszerét vagy az SQL join-t. Például olyan sorokat akarunk törölni a táblázatból, amelyek megfelelnek a következő feltételnek:

El kell, hogy legyenek megfelelő sorok a . táblázatban.

Nézzük meg az alábbi lekérdezést, itt a delete utasítás where záradékában van egy select utasítás. Az SQL Server először megszerzi a select utasításnak megfelelő sorokat, majd az SQL delete utasítással eltávolítja ezeket a sorokat a táblából.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

Alternatívaként használhatunk SQL joint a táblák között, és eltávolíthatjuk a sorokat. Az alábbi lekérdezésben a ] táblákat a táblával kötjük össze. Az SQL join mindig egy közös oszlopon működik a táblák között. Van egy oszlopunk, amely összekapcsolja a két táblát.

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

A fenti törlési utasítás megértéséhez nézzük meg a tényleges végrehajtási tervet.

A végrehajtási terv szerint mindkét táblán táblaszkennelést végez, megszerzi a megfelelő adatokat, és törli őket az Orders táblából.

• Common table expression (CTE)

Common table expression (CTE) segítségével törölhetjük a sorokat egy SQL-táblából is. Először definiálunk egy CTE-t, hogy megkeressük az eltávolítani kívánt sort.

Ezután a CTE-t összekapcsoljuk az SQL-tábla Rendelkezéseivel, és töröljük a sorokat.

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;

Hatások az azonosságtartományra

Az SQL Serverben az azonosságoszlopok egyedi, szekvenciális értékeket generálnak az oszlophoz. Elsősorban az SQL-táblában lévő sorok egyedi azonosítására szolgálnak. Az elsődleges kulcs oszlop jó választás egy fürtözött indexhez is az SQL Serverben.

Az alábbi szkriptben van egy táblánk. Ennek a táblának van egy azonosító oszlopa, az id.

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

Ezzel a táblával 50 rekordot szúrtunk be, ami létrehozta az azonosító értékeket az id oszlophoz.

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

Ha törölünk néhány sort az SQL táblából, az nem állítja vissza a későbbi értékek azonosító értékeit. Töröljünk például néhány sort, amelyeknek az azonosító értékei 20 és 25 között vannak.

Delete from employee where id between 20 and 25

Most nézzük meg a táblázat rekordjait.

Select * from employee where id>15

Ez mutatja az azonosító értéktartományban lévő hiányt.

Az SQL delete utasítás és a tranzakciós napló

Az SQL delete minden sortörlést naplóz a tranzakciós naplóban. Tegyük fel, hogy több millió rekordot kell törölni egy SQL-táblából. Nem szeretnénk nagyszámú rekordot egyetlen tranzakcióban törölni, mert ez exponenciálisan megnövelheti a naplófájlt, és az adatbázis is elérhetetlenné válhat. Ha menet közben törli a tranzakciót, a törlési utasítás visszaállítása órákba telhet.

Ebben az esetben mindig kis darabokban törölje a sorokat, és ezeket a darabokat rendszeresen rögzítse. Például törölhet egyszerre egy 10 000 sorból álló köteget, majd rögzítheti azt, és áttérhet a következő kötegre. Amikor az SQL Server commitolja a fürtöt, a tranzakciós napló növekedése ellenőrizhető.

Legjobb gyakorlatok

• Az adatok törlése előtt mindig készítsen biztonsági mentést.
• Az SQL Server alapértelmezés szerint implicit tranzakciókat használ, és a felhasználó megkérdezése nélkül commitolja a rekordokat. Legjobb gyakorlatként explicit tranzakciót kell indítania a Begin Transaction (Tranzakció indítása) paranccsal. Ez megadja a vezérlést a tranzakció rögzítésére vagy visszaállítására. Akkor is érdemes gyakori tranzakciós napló mentéseket futtatni, ha az adatbázis teljes helyreállítási módban van.
• A túlzott tranzakciós naplóhasználat elkerülése érdekében az adatokat kis darabokban érdemes törölni. Ezzel elkerülhető más SQL tranzakciók blokkolása is.
• A jogosultságokat korlátozni kell, hogy a felhasználók ne tudjanak adatokat törölni. Csak a jogosult felhasználóknak legyen hozzáférése adatok törléséhez egy SQL-táblából.
• A törlési utasítást where záradékkal szeretné futtatni. Szűrt adatokat távolít el egy SQL-táblából. Ha az alkalmazás gyakori adattörlést igényel, érdemes az azonosító értékeket rendszeresen visszaállítani. Ellenkező esetben az identitásértékek kimerülésével kapcsolatos problémákkal szembesülhet.
• Ha ki akarja üríteni a táblát, célszerű a truncate utasítást használni. A truncate utasítás eltávolítja az összes adatot a táblából, minimális tranzakciós naplózást használ, visszaállítja az identitásérték-tartományt, és gyorsabb, mint az SQL delete utasítás, mivel azonnal felszabadítja a tábla összes lapját.
• Amennyiben idegenkulcs-kényszereket (szülő-gyermek kapcsolat) használ a tábláiban, akkor a sort először a gyermek sorból, majd a szülő táblából kell törölni. Ha törli a sort a szülői sorból, akkor a cascade on delete opcióval automatikusan törölheti a sort a gyermek táblából is. Lásd a cikket: Törlési kaszkád és frissítési kaszkád az SQL Server idegen kulcsában további betekintést nyerhet.
• Ha a sorok törléséhez a top utasítást használja, az SQL Server véletlenszerűen törli a sorokat. Mindig a top záradékot kell használnia a megfelelő Order by és Group by záradékkal együtt.
• A törlési utasítás kizárólagos szándékú zárat szerez a hivatkozási táblán, ezért ez idő alatt más tranzakciók nem módosíthatják az adatokat. Az adatok olvasásához használhatja a NOLOCK súgót.
• A tábla súgót ne használja az SQL delete utasítás alapértelmezett zárolási viselkedésének felülírására; csak tapasztalt DBA-k és fejlesztők használhatják.

Fontos megfontolások

Az SQL delete utasításoknak számos előnye van az adatok SQL táblából való eltávolítására, de mint látható, módszeres megközelítést igényel. Fontos, hogy mindig kis tételekben törölje az adatokat, és óvatosan járjon el, amikor adatokat töröl egy termelési példányból. Az adatok minimális időn belüli helyreállítására vonatkozó biztonsági mentési stratégia megléte elengedhetetlen a leállások vagy a jövőbeli teljesítményre gyakorolt hatások elkerülése érdekében.