Best Ways to Use SQL Delete Statement in a SQL Table

Relaatiotietokannoissa luomme taulukoita tietojen tallentamiseksi eri muodoissa. SQL Server tallentaa tiedot rivi- ja sarakemuodossa, jossa on kuhunkin tietotyyppiin liittyvä arvo. Kun suunnittelemme SQL-tauluja, määrittelemme tietotyypit, kuten kokonaisluku, liukuluku, desimaaliluku, varchar ja bit. Esimerkiksi asiakastietoja tallentavassa taulukossa voi olla kenttiä, kuten asiakkaan nimi, sähköpostiosoite, osoite, osavaltio, maa ja niin edelleen. SQL-taululle suoritetaan erilaisia SQL-komentoja, jotka voidaan jakaa seuraaviin luokkiin:

• Data Definition Language (DDL): Näitä komentoja käytetään tietokantaobjektien luomiseen ja muuttamiseen tietokannassa.
  • Luo: Luo objekteja
  • Alter: Muuttaa objekteja
  • Drop: Poistaa objekteja
  • Truncate: Poistaa kaikki tiedot taulukosta
• Data Manipulation Language (DML): Nämä komennot lisäävät, hakevat, muuttavat, poistavat ja päivittävät tietoja tietokantaan.
  • Valitse: Haetaan tietoja yhdestä tai useammasta taulusta
  • Lisää: Lisää uusia tietoja taulukkoon
  • Päivitä: Muuttaa olemassa olevia tietoja
  • Poista: Poistaa taulukon olemassa olevat tietueet

• Data Control Language (DCL): Nämä komennot liittyvät tietokannan oikeuksien tai käyttöoikeuksien valvontaan.
  • Grant: Määrittää oikeudet käyttäjälle
  • Peruuttaa: Peruuttaa oikeudet käyttäjältä
• Transaction Control Language (TCL): Nämä komennot ohjaavat tietokannan tapahtumia.
  • Commit: Tallentaa kyselyn tekemät muutokset
  • Rollback: Palauttaa eksplisiittisen tai implisiittisen transaktion transaktion alkuun tai transaktion sisällä olevaan tallennuspisteeseen
  • Tallenna transaktiot: Asettaa tallennuspisteen tai merkin tapahtuman sisällä

Asetetaan, että sinulla on SQL-tauluun tallennettuja asiakastilaustietoja. Jos lisäisit jatkuvasti tietoja tähän taulukkoon, taulukossa voisi olla miljoonia tietueita, mikä aiheuttaisi suorituskykyongelmia sovelluksissasi. Myös indeksin ylläpidosta saattaisi tulla erittäin aikaa vievää. Usein sinun ei tarvitse säilyttää yli kolme vuotta vanhoja tilauksia. Näissä tapauksissa voit poistaa kyseiset tietueet taulusta. Tämä säästäisi tallennustilaa ja vähentäisi ylläpidon vaivaa.

Voit poistaa tietoja SQL-taulusta kahdella tavalla:

• Käyttämällä SQL:n poistolaskua
• Käyttämällä truncate

Katsomme näiden SQL-komentojen välistä eroa myöhemmin. Tutustutaan ensin SQL:n poistolausekkeeseen.

SQL:n poistolauseke ilman ehtoja

Datanmuokkauskielen (DML) lausekkeissa SQL:n poistolauseke poistaa rivejä taulukosta. Voit poistaa tietyn rivin tai kaikki rivit. Peruspoistolauseke ei vaadi argumentteja.

Luotaan Orders SQL-taulukko alla olevan skriptin avulla. Tässä taulukossa on kolme saraketta , ja .

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

Sijoitetaan muutama tietue tähän taulukkoon.

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

Ja nyt oletetaan, että haluamme poistaa taulukon tiedot. Voit määrittää taulukon nimen tietojen poistamiseksi delete-lauseella. Molemmat SQL-lauseet ovat samat. Voimme määrittää taulukon nimen (valinnaisesta) avainsanasta tai määrittää taulukon nimen suoraan poiston jälkeen.

Delete Orders Go Delete from Orders GO

SQL-poistolauseke suodatetuilla tiedoilla

Nämä SQL-poistolausekkeet poistavat kaikki taulukon tiedot. Yleensä emme poista kaikkia rivejä SQL-taulusta. Jos haluamme poistaa tietyn rivin, voimme lisätä where-lausekkeen delete-lauseeseen. Where-lauseke sisältää suodatuskriteerit ja määrittää lopulta, mikä rivi tai mitkä rivit poistetaan.

Esitetään esimerkiksi, että haluamme poistaa tilauksen id 1. Kun lisäämme where-lausekkeen, SQL Server tarkistaa ensin vastaavat rivit ja poistaa nämä tietyt rivit.

Delete Orders where orderid=1

Jos where-lausekkeen ehto on epätosi, se ei poista yhtään riviä. Poistimme esimerkiksi orders-taulusta tilatun 1:n. Jos suoritamme lausekkeen uudelleen, se ei löydä yhtään where-lausekkeen ehtoa täyttävää riviä. Tällöin se palauttaa 0 riviä, joita asia koskee.

SQL:n poistolauseke ja TOP-lauseke

Rivien poistamiseen voi käyttää myös TOP-lauseketta. Esimerkiksi alla oleva kysely poistaa 100 ylintä riviä Tilaukset-taulusta.

Delete top (100)  from Orders

Koska emme ole määritelleet mitään ’ORDER BY’ -lauseketta, se poimii satunnaiset rivit ja poistaa ne. Voimme käyttää Order by -lauseketta tietojen lajitteluun ja ylimpien rivien poistamiseen. Alla olevassa kyselyssä se lajittelee alenevassa järjestyksessä ja poistaa sen jälkeen taulukosta.

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

Rivien poistaminen toisen taulukon perusteella

Joskus meidän on poistettava rivejä toisen taulukon perusteella. Tämä taulukko saattaa olla olemassa samassa tietokannassa tai ei.

• Taulukon haku

Voidaan käyttää taulukon hakumenetelmää tai SQL-liitosta näiden rivien poistamiseen. Haluamme esimerkiksi poistaa taulusta rivejä, jotka täyttävät seuraavan ehdon:

Taulussa . pitäisi olla vastaavia rivejä.

Katsokaa alla olevaa kyselyä, tässä meillä on select-lause delete-lauseen where-lausekkeessa. SQL Server hakee ensin ne rivit, jotka täyttävät select-lauseen, ja poistaa sitten nämä rivit taulusta SQL delete -lausekkeella.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

Vaihtoehtoisesti voimme käyttää näiden taulujen välisiä SQL-joineja ja poistaa rivit. Alla olevassa kyselyssä yhdistämme taulukot ] taulukon kanssa. SQL join toimii aina taulukoiden välisellä yhteisellä sarakkeella. Meillä on sarake, joka yhdistää molemmat taulukot toisiinsa.

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

Katsotaanpa varsinaista suoritussuunnitelmaa, jotta ymmärrämme edellä esitetyn poistolausekkeen.

Toteutussuunnitelman mukaan se suorittaa molempien taulukoiden taulukkotarkistuksen, hakee vastaavat tiedot ja poistaa ne Orders-taulukosta.

• Yleinen taulukkolauseke (CTE)

Voidaan käyttää yleistä taulukkolauseketta (CTE) myös rivien poistamiseen SQL-taulusta. Ensin määrittelemme CTE:n etsimään rivin, jonka haluamme poistaa.

Sitten yhdistämme CTE:n SQL-taulukon tilauksiin ja poistamme rivit.

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;

Vaikutukset identiteettialueeseen

SQL Serverin identiteettisarakkeet tuottavat sarakkeelle yksilöllisiä, peräkkäisiä arvoja. Niitä käytetään ensisijaisesti SQL-taulukon rivin yksilöimiseen. Ensisijainen avainsarake on myös hyvä valinta klusteroidulle indeksille SQL Serverissä.

Alhaalla olevassa skriptissä meillä on taulukko. Tässä taulukossa on identiteettisarake id.

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

Syötimme tähän taulukkoon 50 tietuetta, jotka tuottivat identiteettiarvot id-sarakkeelle.

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

Jos poistamme SQL-taulukosta muutaman rivin, se ei nollaa identiteettiarvoja seuraaville arvoille. Poistetaan esimerkiksi muutama rivi, joiden identiteettiarvot ovat 20-25.

Delete from employee where id between 20 and 25

Katsellaan nyt taulukon tietueita.

Select * from employee where id>15

Se näyttää identiteettiarvojen vaihteluvälin aukon.

SQL:n poistolauseke ja tapahtumaloki

SQL:n poistolaskenta kirjaa tapahtumalokin jokaiseen rivin poistoon. Oletetaan, että sinun on poistettava miljoonia tietueita SQL-taulusta. Et halua poistaa suurta määrää tietueita yhdellä tapahtumalla, koska se saattaa aiheuttaa lokitiedoston eksponentiaalisen kasvun ja tietokantasi saattaa myös olla käyttökelvoton. Jos peruutat transaktion kesken kaiken, poistolausekkeen peruuttaminen voi viedä tunteja.

Tässä tapauksessa rivit kannattaa aina poistaa pieninä paloina ja sitoa nämä palat säännöllisesti. Voit esimerkiksi poistaa 10 000 rivin erän kerrallaan, sitoa sen ja siirtyä seuraavaan erään. Kun SQL Server sitouttaa lohkon, tapahtumalokin kasvua voidaan hallita.

Parhaat käytännöt

• Tietueet kannattaa aina varmuuskopioida ennen tietojen poistamista.
• Oletusarvoisesti SQL Server käyttää implisiittisiä tapahtumia ja sitouttaa tietueet kysymättä käyttäjältä. Parhaana käytäntönä kannattaa aloittaa eksplisiittinen transaktio komennolla Begin Transaction. Se antaa sinulle hallinnan transaktion sitouttamisesta tai peruuttamisesta. Kannattaa myös tehdä usein tapahtumalokin varmuuskopioita, jos tietokanta on täydessä toipumistilassa.
• Tietoja kannattaa poistaa pieninä kokonaisuuksina, jotta vältytään liialliselta tapahtumalokin käytöltä. Näin vältetään myös muiden SQL-transaktioiden estot.
• Sinun kannattaa rajoittaa käyttöoikeuksia, jotta käyttäjät eivät voi poistaa tietoja. Vain valtuutetuilla käyttäjillä pitäisi olla oikeus poistaa tietoja SQL-taulusta.
• Tahdot suorittaa poistolausekkeen where-lausekkeen kanssa. Se poistaa suodatetut tiedot SQL-taulusta. Jos sovelluksesi vaatii usein tietojen poistamista, on hyvä ajatus nollata identiteettiarvot määräajoin. Muuten saatat joutua kohtaamaan identiteettiarvojen loppumiseen liittyviä ongelmia.
• Jos haluat tyhjentää taulukon, kannattaa käyttää truncate-lauseen käyttöä. Truncate-lause poistaa kaikki tiedot taulusta, käyttää minimaalista tapahtumalokitusta, nollaa identiteettiarvoalueen ja on nopeampi kuin SQL:n delete-lause, koska se poistaa kaikki taulun sivut välittömästi.
• Jos käytät taulukoissasi vierasavainrajoituksia (vanhempi-lapsisuhde), sinun kannattaa poistaa rivi ensin lapsirivistä ja sen jälkeen emotaulusta. Jos poistat rivin vanhemparivistä, voit myös käyttää cascade on delete -vaihtoehtoa poistaaksesi rivin automaattisesti lapsitaulusta. Voit tutustua artikkeliin: Delete cascade and update cascade in SQL Server foreign key for further insights.
• Jos käytät rivien poistamiseen top-lausetta, SQL Server poistaa rivit satunnaisesti. Kannattaa aina käyttää top-lauseketta ja sitä vastaavaa Order by- ja Group by -lauseketta.
• Poistolauseke hankkii yksinoikeudellisen intent-lukituksen viitetauluun, joten sinä aikana muut tapahtumat eivät voi muuttaa tietoja. Voit käyttää NOLOCK-vihjettä tietojen lukemiseen.
• Taulukkovihjeen käyttämistä SQL delete -lausekkeen oletuslukituskäyttäytymisen ohittamiseen kannattaa välttää; sitä saavat käyttää vain kokeneet DBA:t ja kehittäjät.

Tärkeitä näkökohtia

SQL delete -lausekkeiden käyttämisestä tietojen poistamiseen SQL-taulukosta on monia hyötyjä, mutta kuten huomaat, se vaatii metodista lähestymistapaa. On tärkeää poistaa tietoja aina pienissä erissä ja toimia varovaisesti, kun tietoja poistetaan tuotantoinstanssista. Varmuuskopiointistrategia tietojen palauttamiseksi mahdollisimman lyhyessä ajassa on välttämätön, jotta vältetään käyttökatkokset tai tulevat suorituskykyvaikutukset.